mySql将两个SELECT查询与JOIN组合在一起

Question

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 1

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' 

camleCase query fixed:

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            = nested.userId 
       JOIN money_repositories  ON userEwallets.id = money_repositories.ewalletId 

WHERE  a.ewalletNumber = 'SHIRR937303'