CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Mickey Sly
Mickey Sly

Reputation: 429

Joining two tables without combining columns

I have various tables/views that I'm pulling from: Invoice, Detail, contact, party, address and remarks.

Basically I need to pull data from the remarks table and the only way to relate it with the party table is to access the rest of the remarks table to reference its id column which is a foreign key on the party table.

The id is also a column in the Invoice view so when the FROM clause executes it combines that id fields on the Invoice side and there is nothing left to relate to the other remarks columns. Also the names on either table's columns cannot be changed:

 SELECT *
FROM Invoice as invoice_view LEFT OUTER JOIN
  Detail as details ON invoice_view.transact = details.transact LEFT OUTER JOIN
  contact AS co_contact ON invoice_view.company = co_contact.party LEFT OUTER JOIN
  contact AS cp_contact ON invoice_view.company = cp_contact.party INNER JOIN
  party as main_party ON invoice_view.party = main_party.party INNER JOIN
  party as pay_party ON invoice_view.pay = pay_party.party INNER JOIN
  party as rec_party ON invoice_view.rec = rec_party.party LEFT OUTER JOIN

  contact as rec_contact ON rec_party.party = rec_contact.party INNER JOIN
  address as rec_address ON rec_contact.party = rec_address.party AND rec_contact.address = rec_address.addresscode LEFT OUTER JOIN
   contact AS pay_contact ON pay_party.party = pay_contact.party INNER JOIN
   adddress AS pay_address ON pay_contact.party = pay_address.party AND pay_contact.address = pay_address.addresscode AND pay_party.party = pay_address.party LEFT OUTER JOIN
 --What I tried to get it working
  remark as cp_remark CROSS JOIN
 party as custom_party

 WHERE
  (   (custom_party.party = pay_party.party) AND (custom_party.id = cp_remark.id)             OR (cp_remark.id IS NULL) OR (custom_party.remark = 0)) 
 --More where statements that have no affect on this

Upvotes: 0

Views: 2795

Answers (1)

Guvante
Guvante

Reputation: 19213

You need to add an ON condition to your remark join and get rid of that CROSS JOIN. For instance you could do:

LEFT OUTER JOIN remark as cp_remark ON cp_remark.id = main_party.id OR cp_remark.id = pey_party.id OR cp_remark.id = rec_party.id

On a side note, a few recommendations:

  • Don't use SELECT * unless you are writing a query for your own consumption. Your code should show you what data you are using, only include it in the result set.
  • It is personal preference, but I have always found putting your joins on the left is a lot easier than putting them on the right.
  • Order your JOIN in a more logical fashion, your second JOIN requires data from your seventh, try to add detail as you go.
  • You are joining way too many tables, you don't need to join everything that has a foreign key, only the data you are pulling for this exact query.
  • Your final join is a complete join (no ON) and is followed by a CROSS JOIN (these are equivalent), neither of these are a good idea in most cases. Both of these result in the complete results of the table being added to the result set.
  • Foreign keys are used as guidance and for data integrity, they don't define how joins actually perform.
  • Similarly named columns can be resolved by using table.column which you are already doing for your joins.

This is how I would format your original version. (Since I don't know what exact changes would be best for your data set, I didn't include my recommendation for that here)

SELECT *
FROM Invoice as invoice_view
LEFT OUTER JOIN Detail as details ON invoice_view.transact = details.transact
INNER JOIN party as main_party ON invoice_view.party = main_party.party
LEFT OUTER JOIN contact AS co_contact ON invoice_view.company = co_contact.party
LEFT OUTER JOIN contact AS cp_contact ON invoice_view.company = cp_contact.party

INNER JOIN party as rec_party ON invoice_view.rec = rec_party.party
LEFT OUTER JOIN contact as rec_contact ON rec_party.party = rec_contact.party
INNER JOIN address as rec_address ON rec_contact.party = rec_address.party
                                 AND rec_contact.address = rec_address.addresscode

INNER JOIN party as pay_party ON invoice_view.pay = pay_party.party
LEFT OUTER JOIN contact AS pay_contact ON pay_party.party = pay_contact.party
INNER JOIN adddress AS pay_address ON pay_contact.party = pay_address.party
                                  AND pay_contact.address = pay_address.addresscode
                                  AND pay_party.party = pay_address.party
--What I tried to get it working                             
LEFT OUTER JOIN remark as cp_remark
CROSS JOIN party as custom_party
WHERE
((custom_party.party = pay_party.party)
  AND (custom_party.id = cp_remark.id)
   OR (cp_remark.id IS NULL)
   OR (custom_party.remark = 0))
 --More where statements that have no affect on this

EDIT:

Since you asked about what joins do:

  • Join/inner join - only take rows that have data in both tables
  • left join/left outer join - only take rows that have data in the left table (the one not listed), take data from the right if available
  • right join/left outer join - opposite of left join (rarely used since left join is more frequently used)
  • outer join - data in either row creates data in the output
  • join without as/cross join - return every combination of rows between the two tables

For instance:

Table A: 1,2,3,4
Table B: 5,6
Cross join A, B: (1,5),(2,5),(3,5),(4,5),(1,6),(2,6),(3,6),(4,6)

Note that inner joins start as cross joins but are limited by the ON clause. It is basically a way of specifying the conditions of the join in a more convenient location (in the join rather than in the where). Outer joins can be conceptually thought of as inner joins with a blank row added to the optional tables (that matches if and only if no other row matches).

Upvotes: 4

Related Questions