Solved

Join unrelated tables

Posted on 2004-09-10
7
3,098 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

739 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