3 tables - 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

SQL Server 2000 Forums

Transact-SQL (2000)

3 tables

Author

Topic

ovince
Starting Member

32 Posts

Posted - 2006-11-14 : 00:36:09

hi All,

I have 3 tables and I would just like to join them into one, matching 3 columns. Something like this

table1
========
pk1
a1
a2
a3
and other columns

table2
========
pk2
b1
b2
b3
and other columns

table3
========
pk3
c1
c2
c3
and other columns

In the output I should have ONLY data where

a1=b1=c1
a2=b2=c2
a3=b3=c3

I am sure this is easy but would like to be sure that I am doing it correctly and in a most efficient way.

Thanks
Oliver

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-14 : 00:38:00

This is just a case of simple joins. Tell us what have you done so far.

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 00:47:07

The only way I can think of that this requirements will work

quote:
In the output I should have ONLY data where

a1=b1=c1
a2=b2=c2
a3=b3=c3

is following

-- Stage the correct combinaction of first three columns
DECLARE	@Stage TABLE
	(
		col1 INT,
		col2 INT,
		col3 INT
	)

INSERT		@Stage
		(
			col1,
			col2,
			col3
		)
SELECT		col1,
		col2,
		col3
FROM		(
			SELECT		a1 col1,
					a2 col2,
					a3 col3
			FROM		Table1
			UNION ALL
			SELECT		b1,
					b2,
					b3
			FROM		Table2
			UNION ALL
			SELECT		c1,
					c2,
					c3
			FROM		Table3
		) w
GROUP BY	col1,
		col2,
		col3
HAVING		COUNT(*) > 1

-- Do the work
SELECT		t1.<collist>
FROM		Table1 t1
INNER JOIN	@Stage s ON s.col1 = t1.col2 AND s.col2 = t1.col2 AND s.col3 = t1.col3
UNION ALL
SELECT		t2.<collist>
FROM		Table1 t2
INNER JOIN	@Stage s ON s.col1 = t2.col2 AND s.col2 = t2.col2 AND s.col3 = t2.col3
UNION ALL
SELECT		t3.<collist>
FROM		Table1 t3
INNER JOIN	@Stage s ON s.col1 = t3.col2 AND s.col2 = t3.col2 AND s.col3 = t3.col3

Peter Larsson
Helsingborg, Sweden

ovince
Starting Member

32 Posts

Posted - 2006-11-14 : 00:48:44

I did something like this

SELECT *.t1, *.t2, *.t3
FROM Table1 t1, Table2 t2, Table3 t3
WHERE
t1.a1 = t2.b1 AND
t2.b1 = t3.c1 AND
--
t1.a2 = t2.b2 AND
t2.b2= t3.c2 AND
--
t1.a3 = t2.b3 AND
t2.b3 = t3.c3

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 00:54:53

[code]SELECT t1.<collist>,
t2.<collist>,
t3.<collist>
FROM Table1 t1
INNER JOIN Table2 t2 ON t2.col1 = t1.col1 AND t2.col2 = t1.col2 AND t2.col3 = t1.col3
INNER JOIN Table3 t3 ON t3.col1 = t2.col1 AND t3.col2 = t2.col2 AND t3.col3 = t2.col3[/code]
Peter Larsson
Helsingborg, Sweden

ovince
Starting Member

32 Posts

Posted - 2006-11-14 : 01:23:30

thanks for reply,

Do you have an idea how to export the output of Studio Management in ASCII format together with the column names?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 01:30:27

Right click the results windows and choose Save Result As... ?

Peter Larsson
Helsingborg, Sweden

ovince
Starting Member

32 Posts

Posted - 2006-11-14 : 01:42:06

yes but with 'Save Res As' I steel do not have column names in the saved files. Is there some option that has to be adjusted? How do you deal with this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 01:44:33

You set that option in Tools -> Option -> Query Results -> SQL Server -> Results to Grid
and "Include column headers..."

Peter Larsson
Helsingborg, Sweden

ovince
Starting Member

32 Posts

Posted - 2006-11-14 : 01:52:22

thanks Peso...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources