dongshi949737 2016-08-11 12:22
浏览 539
已采纳

mySql将两个SELECT查询与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 ;
  • 写回答

2条回答 默认 最新

  • dox19458 2016-08-11 12:32
    关注

    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' 
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥100 有偿,谁有移远的EC200S固件和最新的Qflsh工具。
  • ¥15 找一个QT页面+目标识别(行人检测)的开源项目
  • ¥15 有没有整苹果智能分拣线上图像数据
  • ¥20 有没有人会这个东西的
  • ¥15 cfx考虑调整“enforce system memory limit”参数的设置
  • ¥30 航迹分离,航迹增强,误差分析
  • ¥15 Chrome Manifest扩展引用Ajax-hook库拦截请求失败
  • ¥15 用Ros中的Topic通讯方式控制小乌龟的速度,走矩形;编写订阅器代码
  • ¥15 LLM accuracy检测
  • ¥15 pycharm添加远程解释器报错