CODEWITHSUNDEEP
phpmysqlsql
Isaque Palmieri
Isaque Palmieri

Reputation: 97

two right joins selects in one query mysql

I need to join two tables, and each must have a common relationship.

One table is for "purchases" and another for "payments", both are directly related to the "customers" table.

I tried that, but it is not working.

SELECT c.id AS idclient, c.name AS nameclient, p.id AS idpayment, cp.id AS idpurchases 
            FROM clients AS c
                RIGHT JOIN payments AS p ON c.id = p.client
                RIGHT JOIN purchases AS cp ON c.id = cp.client

The tables have different structures and other fields that are not currently needed. The fields are basically the ones that are in the select.

Expected outcome:

When a customer is NOT listed in the "purchases" or "payments" tables, it should not be listed, with the exception of a Boolean clause in the "purchases" table (company business rule).

idclient | nameclient | idpurchases | idpayment

109      | John       | 3           | NULL
27       | Code       | 1           | 1
302      | Woen       | 4           | NULL
NULL     | NULL       | 2           | NULL

Upvotes: 0

Views: 219

Answers (2)

Traian GEICU
Traian GEICU

Reputation: 1786

As far as i see you need LEFT JOIN.
Meaning you have clients with no purchases and no payments.
Or the client is doing the purchase but still didn't pay
There are no purchase and no payments with out an client !

SELECT c.id AS idclient, c.name AS nameclient,
       p.id AS idpayment,
       cp.id AS idpurchases 
FROM clients AS c
LEFT JOIN payments AS p ON c.id = p.client
LEFT JOIN purchases AS cp ON c.id = cp.client


Possible output
[idclient,name, idpay,idPur]
[1,A,2,3] - ok
[2,B,null,4] - no payment :ok
[3,C,null,null] - no purchase and no payment : ok

or
use JOIN(inner) to get without null [1,A,2,3]

SELECT c.id AS idclient, c.name AS nameclient,
       p.id AS idpayment,
       cp.id AS idpurchases 
FROM purchases AS cp
//left if wanted all record
//inner : now only all columns populated 
JOIN payments AS p ON cp.client = p.client
JOIN clients AS c ON c.id = cp.client

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95080

You want to join the three tables, but only show clients that have an entry in at least one of the tables payments and purchases. So outer join these tables and use a where clause to ensure at least one of them has a match.

SELECT c.id AS idclient, c.name AS nameclient, p.id AS idpayment, cp.id AS idpurchases 
FROM clients AS c
LEFT JOIN payments AS p ON p.client = c.id
LEFT JOIN purchases AS cp ON cp.client = c.id
WHERE (p.client IS NOT NULL OR cp.client IS NOT NULL);

This is a strange query, however, because the table design suggests there can be multiple payments and multiple purchases per client, but your query combines each payment with each purchase a client made. Aren't they somehow related? Would a client make a payment without having purchased anything? What I am saying is, while you asked for this query, you may really want a completely different one.

Upvotes: 1

Related Questions