'Sub query in join with 3rd table

My Current Join Query

select c.email
       , c.name
       , o.created
       , o.customerId
       , o.code as order_code
       , o.totalValue
       ,'cash' as description
       , o.WalletCash as amount 
from   Order o join Customer c  on o.customerId = c.id 
where  o.WalletCash > 0 
and    o.created BETWEEN '2022-03-16' AND '2022-03-16'

I want to make join or sub query with below query

select customerId from   CustomerWallet where  customerId =100

The Customer, CustomerWalletand Order tables are all associated by CustomerID.

How join 3rd table in join query in mysql?

mysqlsql


Solution 1:[1]

You can alias the first query and use that with the second table.

select * from (select c.email
       , c.name
       , o.created
       , o.customerId
       , o.code as order_code
       , o.totalValue
       ,'cash' as description
       , o.WalletCash as amount 
from   Order o join Customer c  on o.customerId = c.id 
where  o.WalletCash > 0 
and    o.created BETWEEN '2022-03-16' AND '2022-03-16') tab t join CustomerWallet c on t.order_code = c.order_code and c.order_code = like "%abc%"

or you can join all three tables together.

select c.email
       , c.name
       , o.created
       , o.customerId
       , o.code as order_code
       , o.totalValue
       ,'cash' as description
       , o.WalletCash as amount 
from   Order o join Customer c  on o.customerId = c.id 
       join CustomerWallet w on w.order_code = order_code
where  o.WalletCash > 0 
and    o.created BETWEEN '2022-03-16' AND '2022-03-16' 
and w.order_code like "%abc%"

Solution 2:[2]

If you want to restrict the first query's result to customer IDs found in the second query, then use IN:

select c.email
       , c.name
       , o.created
       , o.customerId
       , o.code as order_code
       , o.totalValue
       ,'cash' as description
       , o.WalletCash as amount 
from   Order o join Customer c  on o.customerId = c.id 
where  o.WalletCash > 0 
and    o.created BETWEEN '2022-03-16' AND '2022-03-16' 
and    c.id in
       (
         select customerId 
         from   CustomerWallet 
         where  order_code like '%abc%'
       );

I see that there is an order_code in CustomerWallet. There is a code in your order table, too. Maybe you want to consider this in your lokup. Probably:

and    (o.code, c.id) in
       (
         select cw.order_code, cw.customerId 
         from   CustomerWallet cw
         where  cw.order_code like '%abc%'
       );

IN and EXISTS are appropriate for such lookups. As opposed to a join they just ensure a match exists. With joins you could involuntarily produce duplicate rows in case the second itself returns the join criteria multifold.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Pirate
Solution 2

Related Questions