CODEWITHSUNDEEP
mysqlsqljoin
leo
leo

Reputation: 366

Joining three tables - Mysq-l an inner and outer join perhaps?

I am struggling to get this query to work . I have three tables and I want to do a query to get the red area. Each circle is a table with different structure. I have managed a lot of combinations of inner joins but i specially cant get all the red areas.

A Payments : idPayment , idInvoice , Amount , date.

B Invoice : idInvoice , amount date.

C PromissoryNotes: IdNote , idInvoice, amount, date.

enter image description here

so far ...

SELECT B.idInvoice,A.idPayment,C.idNote FROM (Invoice b INNER JOIN payments a ON a.idInvoice=b.idInvoice) LEFT OUTER JOIN PromissoryNotes c ON c.idInvoice=b.idInvoice ORDER BY idInvoice.

DOESNT QUITE WORK

Any suggestions?

Upvotes: 3

Views: 4213

Answers (5)

airpierre
airpierre

Reputation: 155

SELECT p.idInvoice, p.idPayment, idNote 
FROM Payments p JOIN Invoice i ON p.adInvoice-i.adInvoice RIGHT OUTER JOIN PromissoryNotes
UNION
SELECT i.idInvoice, idPayment, idNote 
FROM Invoice i JOIN PromissoryNotes pn ON i.idInvoice=pn.idInvoice RIGHT OUTER JOIN Payments

You need to include the outer joins because the resulting tables that are to be unioned must have the same schema. I believe these are the desired fields from the query.

Upvotes: 0

mrtig
mrtig

Reputation: 2267

You could do this two ways:

1) Create a set A that is the inner join of B and A, create a set C that is the inner join of B and C, then union A and C.

2) Create a sub query that inner joins A and B, then full outer join to a sub query that inner joins C and B.

Example of 1) 

SELECT  b.idInvoice FROM Invoice B 
JOIN Payments A on A.IdInvoice = B.IdInvoice
UNION 
SELECT  b.idInvoice FROM Invoice B 
JOIN PromissoryNotes C on c.idInvoice = B.id Invoice

Example of 2) 

SELECT idInvoice FROM 
    ( 
        SELECT  b.idInvoice FROM Invoice B 
        JOIN Payments A on A.IdInvoice = B.IdInvoice
    ) B FULL OUTER JOIN 
    (
        SELECT  b.idInvoice FROM Invoice B 
        JOIN Payments A on A.IdInvoice = B.IdInvoice
    ) C on b.idInvoice = C.idInvoice

Upvotes: 2

bf2020
bf2020

Reputation: 732

Does this do the trick?

SELECT 
ZZ.idInvoice,
ZZ.idPayment,
YY.idInvoice,
YY.idNote 
FROM
(SELECT idInvoice, idPayment
    FROM Invoice b 
        INNER JOIN payments a ON a.idInvoice=b.idInvoice) AS ZZ
FULL OUTER JOIN
(SELECT idInvoice, idNote
    FROM PromissoryNotes c 
        INNER JOIN payments a ON a.idInvoice=c.idInvoice) AS YY ON ZZ.idInvoice = YY.idInvoice

Upvotes: 0

sgeddes
sgeddes

Reputation: 62861

You were pretty close -- another OUTER JOIN and some WHERE criteria will do the trick:

SELECT B.idInvoice, A.idPayment, C.idNote 
FROM Invoice b 
    LEFT JOIN payments a ON a.idInvoice=b.idInvoice
    LEFT JOIN PromissoryNotes c ON c.idInvoice=b.idInvoice 
WHERE a.idInvoice IS NOT NULL 
    OR c.idInvoice IS NOT NULL
ORDER BY B.idInvoice

What this basically says is give me all results from table B, where there's a match in table a or table c.

Upvotes: 4

Benedikt
Benedikt

Reputation: 607

Try

SELECT B.idInvoice, A.idPayment, C.idNote FROM Invoice B INNER JOIN payments A ON        A.idInVoice = B.idInvoice INNER JOIN PromissoryNotes C ON C.idInvoice = B.idInvoice ORDER BY idInvoice

INNER JOIN means you get the intersection of both tables. So this is what you want.

Upvotes: 0

Related Questions