Joing Tabels Queryf

[dg3249]

I have three tables that I need to join and I am having some trouble with it. Please help..

Tables:

Client Orders NA_Orders
OFFICE_ID OFFICE_ID OFFICE_ID
group_id Order_id Order_id
group_id

Now, these three tables have the same field (office_id) but they can all have a different value. How can a write a join query to show all values from these three tables
Diana

 

[tlbroadbent]

You can only JOIN tables if there is a column with matching data in all of the tables. The general structure of the JOIN is explained in SQL BOL.

http://msdn.microsoft.com/library/en-us/tsqlref/ts_sa-ses_9sfo.asp

http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_09_610z.asp

Example:
Select
c.OFFICE_ID,
c.Group_id,
rder_id
From Client c
Inner Join Orders o
On c.Office_ID = ffice_ID
And c.Group_ID = o.Group_ID
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[JohnDTampaBay]

It appears as though Client and Orders could join on Group_ID, then Orders and NA_Orders could join on Order_ID, giving you something like ....

SELECT c.Office_ID, c.Group_ID, 
  o.Office_ID, o.Order_ID, 
  n.Office_ID
FROM Clients c
  JOIN Orders o ON c.Group_ID = o.Group_ID
    JOIN NA_Orders n ON o.Order_ID = n.Order_ID

Is this what you're looking for? Good luck! --Angel
-----------------------------------
SELECT * FROM users WHERE clue > 0
0 row(s) affected

 

[dg3249]

I understand what you both have suggested, but its not exactly what I need. orders.office_id and na_orders.office_id may not necessarily have the same office_id for the same order, but one of those two fields will match with clients.office_id. So essentially what I need is to show all clients and their office_id's as well if they have any orders and their office_id is different than the clients.office_id then show it. I know its a little bit of a weird one.

Diana

 

[JohnDTampaBay]

Possibly a UNION?

SELECT c.Group_ID, c.Office_ID, o.Order_ID
FROM Clients c
  JOIN Orders o ON c.Office_ID = o.Office_ID
UNION
SELECT c.Group_ID, c.Office_ID, n.Order_ID
FROM Clients c
  JOIN NA_Orders n ON c.Office_ID = n.Office_ID

--Angel
-----------------------------------
SELECT * FROM users WHERE clue > 0
0 row(s) affected

 

[lespaul]

Just browsing through and couldn't help but notice your tag line Angel!!

Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[JohnDTampaBay]

I noticed yours also.

Great minds think alike. --Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[r937]

have you guys seen the t-shirt?

http://www.thinkgeek.com/tshirts/coder/595d

rudy

 

[lespaul]

Rudy - I own the t-shirt!!!

Have you seen:

There are 10 types of people in the world -
those who understand binary and those who don't!!


Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[dg3249]

Hi Angel,

I have created a Union query and it seems to pull up the data correctly.
Now, I have other fields that I need to include as part of the query, how would you go about this without including it in the union itself.

D

 

[JohnDTampaBay]

If you need to include other fields in the result set, the additional columns will need to be added to each SELECT statement. If you have different inclusion criteria for Orders versus NA_Orders, modify the WHERE clause in the appropriate SELECT statement. You can even join to other tables, if necessary.

The key to UNION is to have identically structured result sets from your multiple SELECTs. The UNION will append one set to another and return a single result set. Note that UNION is distinct across the individual SELECTs. If the exact same result row is returned from two different SELECTs, only one instance will appear with the UNION. Use UNION ALL to see all instances. --Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.