CODEWITHSUNDEEP
phpmysql
M.H
M.H

Reputation: 57

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 ;

Upvotes: 0

Views: 72

Answers (2)

bencagri
bencagri

Reputation: 183

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'

Upvotes: 1

jonju
jonju

Reputation: 2736

Because you are not including table userEwallets in the query except in the sub query which won't be recognized outside the subquery. Try

UPDATE

Select 
     users.id as userId, userEwallets.id As ewalletId,money_repositories.money
From
    users
Inner Join 
    userEwallets ON userEwallets.userId= users.id
Inner Join 
    money_repositories ON money_repositories.userId=users.id
And
    money_repositories.ewalletId = userEwallets.id
Where
    userEwallets.ewalletNumber = 'SHIRR93730'

Upvotes: 0

Related Questions