Gossamer Forum
Home : General : Databases and SQL :

Joining Tables in a query...

Quote Reply
Joining Tables in a query...
I currently have two tables I successfully perform keyword searches on. I now want to add a third table to the same query. Since I'm just an amatuer trying to solve someone elses mess, I having difficulty figuring this one out myself. This is what I have...

table 1: "Company" column 1: "CompanyID"

table 2: "Contact" column 1: "ContactID"

column 2: "ProductLines"

Original working query:

SELECT * FROM Company LEFT JOIN Contact on Company.CompanyID=Contact.ContactID

I wish to add a third table...

table 3: "ProductItems" column 1: "ProductLines"

column 2: "ProductItems"



The thing I'm having trouble with is how to nest these tables correctly. The reason for 3 separate tables is that each "Contact" carries many different "ProductItems" under many different "ProductLines", and, each "Contact" may serve more than one "Company". I already do keyword searches on companies and product lines, but I now want to add this third table to extend the same keyword search to the data in the product item fields.

I hope all this made sense.
Quote Reply
Re: [jharwood] Joining Tables in a query... In reply to
Since Contact contains Foreign "keys" with the two other tables, use that table first in JOIN (also, you should specify which * all columns you want to select from)...

LIKE:

Code:
SELECT co.*, ct.*, pi.*
FROM Contact ct LEFT JOIN Company co ON co.ContactID = ct.ContactID
LEFT JOIN ProductItems pi ON pi.ProductLines ON ct.ProductLines

Also your FK and PK names seem out of whack.
========================================
Buh Bye!

Cheers,
Me

Last edited by:

Stealth: Sep 21, 2003, 8:20 PM
Quote Reply
Re: [Stealth] Joining Tables in a query... In reply to
Thanks again Stealth for your quick responses.

The query you outline here worked, but I didn't get the results I was expecting. Not your fault though, after seeing the results and then re-reading my original question, I think I now need to take a little more time to figure out how to explain better what I'm trying to do here.

Till then, thanks again.
Quote Reply
Re: [jharwood] Joining Tables in a query... In reply to
I think this is what I need, a table like the following...


-------+--------------+-----------+

com con pi

-------+--------+-----+-----+-----+

comID conID pl pl pi

-------+--------+-----+-----+-----+

2 2 240 240 1

blank 2 240 240 2

blank 2 240 240 3

3 3 240 240 1

blank 3 240 240 2

blank 3 240 240 3

4 4 100 100 1

blank 4 100 100 2

blank 4 100 100 3

5 5 79 79 1

blank 5 79 79 2

blank 5 79 79 3

blank 5 79 79 4

6 6 100 100 1

blank 6 100 100 2

blank 6 100 100 3



I want a table that gives me all the keywords found in the "pi"'s in a keyword search, but when I do "com" search on the same table I don't want to return multiple "com"'s. It seems to me that I need an "OUTER JOIN" between the tables "com" & "con", and, a "LEFT JOIN" between the tables "con" & "pi". But I just can't seem to get the syntax right.
Quote Reply
Re: [jharwood] Joining Tables in a query... In reply to
You can also use the DISTINCT function to get distinct records and also make your query dynamic (using conditions in the FROM section) in terms of what is inputted from the search form.
========================================
Buh Bye!

Cheers,
Me