Solved

Join unrelated tables

Posted on 2004-09-10
7
3,015 Views
Last Modified: 2010-10-11
Hi,

I have two tables that are technically unrelated but I would like them all returned together in one big query.

Obviously if I try and do a normal SQL statement combining the two it results in a cartesian join.

I can't use Union as the return columns differ between the tables.

I am quite happy with NULL's in the columns where that table doesnt have that data.

Is this possible?

Thanks.

James.
0
Comment
Question by:JAMES
7 Comments
 
LVL 18

Accepted Solution

by:
ShogunWade earned 500 total points
ID: 12025832
you can do it with a union but you need some placeholder columns

eg:

table1 as col1 and table2 has col2

SELECT col1,NULL from table1
UNION ALL
SELECT NULL,col2 from table2
0
 
LVL 6

Expert Comment

by:mcp111
ID: 12025894
You can use outer joins.
If you have two tables tbl1 and tbl2,

Left join will return all records from tbl1 and matching from tbl2
Right join will return all records from tbl2 and matching from tbl1.

If you post detailed table structures and desired results I can help you with the exact query.
0
 

Author Comment

by:JAMES
ID: 12025899
How totally cool was that answer - works a treat...

Thanks a bunch.

James.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 7

Expert Comment

by:FDzjuba
ID: 12025901
join

SELECT table1.col1,table2.col1,table2.col2 FROM table1 INNER JOIN table2 ON table1.id=table2.id


combine everything without any rules
SELECT table1.*,table2.* FROM Table1,Table2
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12025910
glad to help :)

0
 

Author Comment

by:JAMES
ID: 12025921
Thanks guys but ShogunWade has the answer....

mcp111 and FD - cant use joins as I stated the two tables were unrelated.

FD - if you use your second example you create a cartesian join which I had already experienced.

Thanks all.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12025934
PS.   FD,   more modern syntax for SELECT table1.*,table2.* FROM Table1,Table2   is SELECT * FROM Table1 CROSS JOIN Table2
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now