help with join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help with join

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2009-11-12 : 01:28:04

I need help with joining multiple tables.

--Lookup table, I want to join the tables below using the
--rows in table1.
table1
table1_id int
table1_col varchar

table2 --parent table
table2_id (pk)
table2_col

--child table to table2, can contain multiple references
--to table2 or none.
table3
table3_id
fk_table2_id (fk to table2 id)
table3_col

I want to fetch all rows in table2 match table1
If table3 does not have an rows on that table2_id then

Example:
table1
1, 'table1_test1'
2, 'table1_test2'
3, 'table1_test3'

table2
1, 'table2_test1'
2, 'table2_test2'
3, 'table2_test3'
4, 'table2_test4'
5, 'table2_test5'

table3
1, 1, 'table3_test1'
1, 2, 'table3_test1'

Wanted output:
table1_id
table1_col
table2_col
table3_id
fk_table3_id
table3_col

Display all rows from table2 matching table1.
When rows are missing in table3 when joining against table2, show all rows that match table1.

I know I am going to use a outer join betewwn table2 and table3, but I only want to show the is that matches table1.

I know my description is very unclear and for that i apologize.

krishnarajeesh
Yak Posting Veteran

67 Posts

Posted - 2009-11-12 : 08:51:32

"fetch all rows in table2 match table1" , am sorry I couldnt see any relation to write a match between these 2 tables. Could you be more clear pls.

Thanks,
Krishna
www.SQLServer.in

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-12 : 11:41:06

Well you need to make it clear

Please post the DDL of the actual tables...Do you know how to generate a script?

CREATE TABLE <tablename>....

Then Post some sample data in DML Form

INSERT INTO TABLE1 (Collist)
SELECT <data> UNION ALL
SELECT <data> UNION ALL
etc

Then Post what the sample result set should look like

Read the hint link in my sig for more of an explanation

Thanks

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources