-4

I need to apply a join to 3 tables (invoice, payments, client) on the client ID; however, the join I need must include all the invoices and all the payments associated with a client ID. If we have a client that has a payment and no invoices (or the other way around) this column should also appear. I don't want duplicates in the resulting table, so if a client has both invoice and payment he should appear only once. I could use a cross join and then select distinct but is there a join that can do that directly.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2497624
  • 159
  • 9
  • please post your tablestructure – Obl Tobl Jun 27 '13 at 14:14
  • client ( client ID, preneurID, tot_DEBIT, TOT_CREDIT, TOTAL, ...) INVOICE (invoiceID, debit, date .....) payment(payment ID, credit, date....) how is it related to my question? I just need a join that will take all the rows from different tables, merge the ones with the same ID and keep the ones that are unique to a table – user2497624 Jun 27 '13 at 14:20
  • 1
    Very good! Now the other two tables, then maybe someone can help you... and please edit your question with the tablestructures and don't just add it as comment. – Obl Tobl Jun 27 '13 at 14:23

1 Answers1

1

Something like

Select * From T1
left join T2 On T1.ClientID = T2.ClientID
Left join T3 On T1.ClientID = T3.ClientID

should do that.

Tony Hopkinson
  • 20,172
  • 3
  • 31
  • 39
  • I knew abouthe left outer join, but never heard (or forgot) about the left join. Thanks for your time – user2497624 Jun 27 '13 at 14:31
  • Left join is left outer join. Outer has been dropped/ deprecated out of the syntax. Now inner join, left or right join, usually, depending on DBMS of course. – Tony Hopkinson Jun 27 '13 at 14:33
  • After reading, i noticed that the two are actualy similar i was the one who didn't understand their mechanism correctly. I always assumed it would only take the values of the left table that are unique to the left table. – user2497624 Jun 27 '13 at 14:44
  • That's an inner join. I believe the changed the syntax, because left and right are only relevant to outer joins. – Tony Hopkinson Jun 27 '13 at 23:24