Question 221700 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to get the records of X and its referrals in one query?

I have got 3 tables.

Orders                       Clients                    Referrals
-----                        -----                      -----
id                           id                         id
total_price                  name                       name
receiver_client_id           status                     client_id

While adding order, sometimes the receiver is CLIENT, sometimes REFERRAL, so receiver_client_id can have both client_id or referral_id. Referrals are added by Clients, that’s why I store client_id in referrals table.

I want to get all the orders of a client who’s id is "15032" as well as it’s referrals orders in one query. Is it possible?

Sample data:

Orders                 Clients                  Referals
----                   ----                     ----
1, 350, 15032          15010, "Alex", 1         7809100, "Jay", 15010
2, 440, 16500          15011, "Jacob", 1        7809101, "Jean",15010
3, 210, 18023          15012, "Sam", 0          7809102, "Hew", 15011
4, 900, 15032          15032, "John", 1         7809123, "Aldo",15032
5, 330, 7809123        15040, "Sandy", 0        7809124, "Sew", 15032

Expected result:

1, 350, 15032
4, 900, 15032
5, 330, 7809123
Tags:

2

Answers


  1. 0

    Try the query below

    SELECT *
FROM Orders o
JOIN Clients c ON c.id = o.receiver_client_id
LEFT JOIN Referrals r ON r.client_id = c.id
WHERE c.id = 15032

    Then you UNION the Clients and Referrals table first. This will get you orders from both clients and referrals

    SELECT *
FROM (
    SELECT id, name
    FROM Clients
    UNION 
    SELECT DISTINCT id, name
    FROM Referrals
) t
JOIN Orders o ON o.receiver_client_id = t.id
    Login or Signup to reply.
  2. 0

    All you should need to do is UNION two tables together, one for the orders, and one for the referrals. To get the orders where the referral number links back to the client id, a JOIN will be needed:

    SELECT 
*
from orders where receiver_client_id in ($receivers)
UNION
select * from
(
  SELECT o.id, o.total_price, o.receiver_client_id 
  from orders
  inner join referrals r
  on o.receiver_client_id = r.id
  where r.client_id in ($receivers)
)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search