Transact SQL Help

[retired_member34]

I am actually using SQL Server 2005, but I am sure this is such a fundamental simple problem that any Transact SQL would do. If anyone knows the answer I'd really appreciate the help - I am missing something very simple here!

I have 3 or more tables with various columns, but all having 1 column in common. I want to perform a query that picks the top x records, or from x+1 to y, from table 1 when ordered by the common column, then data from table 2 and table 3 using the same column. I can do it with 2 tables using Top and a Join, but struggle extending it beyond 2 tables. So I currently have:


SELECT TOP 6 table1.CommonColumn, table1.AnotherColumn, table2.DifferentColumn1, table2.DifferentColumn2

FROM table1

Join table2

On table1.CommonColumn = table2.CommonColumn

WHERE table1.SomeColumn = Value

AND table1.CommonColumn NOT IN (SELECT TOP 12 table1.CommonColumn

FROM table1

WHERE table1.SomeColumn = Value)

order by table1.CommonColumn



I need to extend it to return table3.DifferentColumn3, table3.DifferentColumn4 with table1.CommonColumn = table3.CommonColumn. It's POETS day and I would rather be sloping off to the pub for a sneaky pint. Any help very gratefully received....

 

[GlasgowBob]

Hi, how about doing this instead of joining the tables?

1 create a temp table
2 insert x rows from table 1
3 insert x rows from table 2
4 insert x rows from table 3
5 select * from temp table

 

[retired_member34]

Hi, how about doing this instead of joining the tables?

1 create a temp table
2 insert x rows from table 1
3 insert x rows from table 2
4 insert x rows from table 3
5 select * from temp table

Hmm, ok. The first query is easy, it's a subset of my query above. I would then need to query table2 based upon the CommonColumn in the temporary table, and not insert extra rows but update the existing rows in the temp table inserted from table1 with columns from table2, and then repeat. Is this just a query on table2 with a subquery using CommonColumn in the temp table? My transact sql is crap.

Thanks very much for the reply, I'll give it a go! Cheers Bob.

 

[stevebrowne]

SELECT TOP 6 table1.*, table2.*, table3.*

FROM table1

Join table2 On table1.CommonColumn = table2.CommonColumn
Join table3 On table1.CommonColumn = table3.CommonColumn

WHERE table1.SomeColumn = Value

etc..


you might need FULL OUTER JOIN instead of just join if you don't have the same commoncolumn in all 3 tables but still want a reecord returned.

 

[GlasgowBob]

Sorry sounds like your trying to do something more complicated than I thought. I'm not that great with SQL either so hope you get the answer

 

[retired_member34]

SELECT TOP 6 table1.*, table2.*, table3.*

FROM table1

Join table2 On table1.CommonColumn = table2.CommonColumn
Join table3 On table1.CommonColumn = table3.CommonColumn

WHERE table1.SomeColumn = Value

etc..

you might need FULL OUTER JOIN instead of just join if you don't have the same commoncolumn in all 3 tables but still want a reecord returned.

Steve, many thanks you are a whiz. I had previously tried what you stated but it was only returning a single record. I now realise it was only returning records that existed in table3, whereas I wanted a return of table1 and table2 even if there was no match in 3 - your FULL OUTER JOIN did just this. Result.

Thanks again.