M.H M.H - 3 months ago 8
MySQL Question

mySql combine two SELECT queries with JOIN

I have two mySql queries to get result from databases. I am trying to join them together.

Query 1:

SELECT userEwallets.id as ewalletId, users.id as userId , money_repositories.money as money, a.nestedUserId
FROM userEwallets
JOIN users ON users.id = userEwallets.userId
JOIN money_repositories ON userEwallets.id = money_repositories.ewalletId
WHERE ewalletNumber = 'SHIRR937303656'


Query 2:

SELECT nested.id as nestedUserId
FROM userEwallets as nested
JOIN users ON users.id = nested.userId
JOIN money_repositories ON nested.id = money_repositories.ewalletId
WHERE ewalletNumber = 'SHIRR9122331743'


and then my combination command:

SELECT userEwallets.id as ewalletId, users.id as userId , money_repositories.money as money, a.nestedUserId
FROM (
SELECT nested.id as nestedUserId
FROM userEwallets as nested
JOIN users ON users.id = nested.userId
JOIN money_repositories ON nested.id = money_repositories.ewalletId
WHERE ewalletNumber = 'SHIRR912233'
) as a
JOIN users ON users.id = userEwallets.userId
JOIN money_repositories ON userEwallets.id = money_repositories.ewalletId
WHERE ewalletNumber = 'SHIRR93730'


I get this error:

#1054 - Unknown column 'userEwallets.id' in 'field list'


Both of commands are same but they have simple difference as
ewalletNumber
in
where
clause

UPDATE WITH DATABASE STRUCTURE

money_repositories
table:

CREATE TABLE IF NOT EXISTS `money_repositories` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` int(11) NOT NULL,
`ewalletId` int(11) NOT NULL,
`money` int(11) NOT NULL,
`createdAt` int(11) NOT NULL,
`updatedAt` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci AUTO_INCREMENT=4 ;


userEwallets
table:

CREATE TABLE IF NOT EXISTS `userEwallets` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` int(11) NOT NULL,
`ewalletNumber` varchar(15) COLLATE utf8_persian_ci NOT NULL,
`currencySymbol` varchar(5) COLLATE utf8_persian_ci NOT NULL,
`createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`updatedAt` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci AUTO_INCREMENT=7 ;


users
table:

CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(20) COLLATE utf8_persian_ci NOT NULL,
`password` varchar(65) COLLATE utf8_persian_ci NOT NULL,
`name` varchar(20) COLLATE utf8_persian_ci NOT NULL,
`family` varchar(20) COLLATE utf8_persian_ci NOT NULL,
`birthDay` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
`email` varchar(20) COLLATE utf8_persian_ci NOT NULL,
`mobileNumber` varchar(15) COLLATE utf8_persian_ci NOT NULL,
`verifyCode` varchar(5) COLLATE utf8_persian_ci NOT NULL,
`photoUri` varchar(50) COLLATE utf8_persian_ci NOT NULL,
`ebanNumber` varchar(20) COLLATE utf8_persian_ci NOT NULL,
`status` tinyint(1) NOT NULL,
`createdAt` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updatedAt` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci AUTO_INCREMENT=35 ;

Answer

userEwallets.id should be a.id in Select of your combination command. Because you are getting subquery as a.

Modified combined query;

SELECT a.id                     AS ewalletId, 
       users.id                 AS userId, 
       money_repositories.money AS money, 
       a.nesteduserid 
FROM   (SELECT nested.id AS nestedUserId, 
               nested.id, 
               nested.ewalletnumber 
        FROM   userewallets AS nested 
               JOIN users 
                 ON users.id = nested.userid 
               JOIN money_repositories 
                 ON nested.id = money_repositories.ewalletid 
        WHERE  nested.ewalletnumber = 'SHIRR912233') AS a 
       JOIN users 
         ON users.id = userewallets.userid 
       JOIN money_repositories 
         ON userewallets.id = money_repositories.ewalletid 
WHERE  a.ewalletnumber = 'SHIRR93730' 
Comments