CODEWITHSUNDEEP
mysqlsql-serverjoin
hungrykoala
hungrykoala

Reputation: 1083

how to use left outer join after left join for this tables?

I have 4 tables that I want to join in order to get the necessary data in them.

Table A.

id | name | deleted | amount | due_date   | status
1  | a    | 0       | 10     | 2016-07-18 | Unpaid
2  | b    | 0       | 20     | 2016-07-19 | Unpaid
3  | c    | 0       | 15     | 2016-07-18 | Unpaid

Table B

id | name   | due_date   | status
1  | a      |            | Unpaid
2  | b      |            | Unpaid
3  | c      |            | Unpaid
4  | d      | 2016-07-19 | Unpaid

Table C

id | table_d_id | table_a_id
1  | 1          | 1
2  | 2          | 2
3  | 3          | 3

Table D

id | 
1
2
3

What I want to do here is to retrieve the data at Table B with due_date and an unpaid status, and that it is not found in Table A.

My current query:

SELECT  A.*, B.*, C.*, D.*  
FROM A  
LEFT OUTER JOIN B ON B.name= A.name 
INNER JOIN C ON A.id = C.id  
INNER JOIN D ON C.id = D.id  
WHERE A.deleted=0 AND ((B.due_date < "2016-07-20" AND B.status = "Unpaid") OR (A.due_date < "2016-07-20" AND A.status = "Unpaid")) 
GROUP BY D.id

What this query currently does is that it only gets the data in the tables except for id 4 at table B, Which I was hoping to retrieve also since it has an unpaid status.

Upvotes: 0

Views: 659

Answers (3)

The Shooter
The Shooter

Reputation: 733

Try this:

SELECT  A.*, B.*, C.*, D.*  
FROM B  
LEFT OUTER JOIN A ON A.name= B.name 
LEFT OUTER JOIN C ON C.id = B.id  
LEFT OUTER JOIN D ON D.id = B.id  
WHERE A.deleted=0 AND ((B.due_date < "2016-07-20" AND B.status = "Unpaid") OR (A.due_date < "2016-07-20" AND A.status = "Unpaid")) 
GROUP BY D.id

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

You should use left join (and in you sample the b.column is name not number ..)

SELECT  A.*, B.*, C.*, D.*  
FROM B  
LEFT JOIN A_cstm ON A.id = A_cstm.id_c  
LEFT  JOIN A ON B.name = A.name 
INNER JOIN C ON A.id = C.id  
INNER JOIN D ON C.id = D.id  
WHERE A.deleted=0 AND ((B.due_date < "2016-07-20" AND B.status = "Unpaid") OR (A.due_date < "2016-07-20" AND A.status = "Unpaid")) 
GROUP BY D.id

The table A_cstm is not described in you model (i have removed for clarity ..) and i have change the order of th join using FROM B and left join A instead of your FROM A .. left join B

SELECT  A.*, B.*, C.*, D.*  
FROM B  
LEFT  JOIN A ON B.name = A.name 
INNER JOIN C ON A.id = C.id  
INNER JOIN D ON C.id = D.id  
WHERE A.deleted=0 AND ((B.due_date < "2016-07-20" AND B.status = "Unpaid") OR (A.due_date < "2016-07-20" AND A.status = "Unpaid")) 
GROUP BY D.id

Upvotes: 1

Dhaval Bhavsar
Dhaval Bhavsar

Reputation: 495

Please use below query

SELECT  A.*, B.*, C.*, D.*  
FROM A  
LEFT JOIN A_cstm ON A.id = A_cstm.id_c  
LEFT JOIN B ON B.number = A.name 
INNER JOIN C ON A_cstm.id_c = C.id
INNER JOIN D ON C.table_d_id = D.id  
WHERE A.deleted=0 AND ((B.due_date < "2016-07-20" AND B.status = "Unpaid") OR (A.due_date < "2016-07-20" AND A.status = "Unpaid")) 
GROUP BY D.id

Upvotes: 1

Related Questions