?
Solved

Join unrelated tables

Posted on 2004-09-10
7
Medium Priority
?
3,221 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 2000 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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.
Suggested Courses

864 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