If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > ANSI SQL > SQL Query - SELECT from multiple tables

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Sep 2004
Posts: 10
SQL Query - SELECT from multiple tables

Hi,

I want to select from two SQL tables using the following statement:

SELECT * from table1,table2

each table only has 50 rows, and when I SELECT them individually I get the correct number of rows returned. BUt when I execute the above statement to select from both tables, I am getting around 1000 rows returned with many duplicate entries. Is there a way to get around that?

Thanks.
Reply With Quote
  #2 (permalink)  
Old
Village Idiot
 
Join Date: Jul 2003
Location: Michigan
Posts: 1,941
Your seeing the cartesian result (for every row in table1 - show every row in table2). If you had 50 rows in each table, you'd see 2500 rows (50x50).

You need to use a join of some kind. Depends on if you want to see only those rows that match from both tables, or all the rows in one table and those that match from the other table.
__________________
Inspiration Through Fermentation
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Sep 2004
Location: Belgium
Posts: 1,126
Maybe you want to see the two tables one below the other?
This is only possible if the two tables have the same structure (i.e., the same number of columns, with the same interpretation).
If that is what you want, viz. a result table of 100 rows, write
Code:
SELECT *
FROM   table1
UNION ALL
SELECT *
FROM   table2
__________________
--_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting
__http://www.abis.be/
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Sep 2004
Posts: 10
Is it possible to see one table below another when they share not all but some similar fields (name, id, etc)? I guess the alternative is to have two SQL statements (one for each table) each time I want to query something. But that seems more cumbersome than just having one SQL statement.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Sep 2004
Location: Belgium
Posts: 1,126
Quote:
Originally Posted by 7thblue
Is it possible to see one table below another when they share not all but some similar fields (name, id, etc)?
Of course; but only if you either limit the longer table to the common columns, or you extend the other one (with constant values). E.g.:
Code:
SELECT name, id, col3
FROM table1
UNION ALL
SELECT name, id, '--'
FROM table2
__________________
--_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting
__http://www.abis.be/
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,721
Quote:
Originally Posted by 7thblue
Is it possible to see one table below
Wrong question. There is no "below". Tables are sets of rows, and sets are not ordered per definition. You must use an ORDER BY clause if you want to have some sort of defined order for the rows. There you could do this:
Code:
SELECT 1 AS o, a.*
FROM   yourTable AS a
WHERE  ...
UNION ALL
SELECT 2 AS o, b.*
FROM   yourOtherTable AS b
WHERE  ...
ORDER BY o, ...
Quote:
... another when they share not all but some similar fields (name, id, etc)? I guess the alternative is to have two SQL statements (one for each table) each time I want to query something. But that seems more cumbersome than just having one SQL statement.
Besides aligning the table structures for the UNION operation in the way Peter outlined, you could use a full outer join with a join condition that is always invalid:
Code:
SELECT ...
FROM   table1 FULL OUTER JOIN table2 ON ( 1 = 0 )
__________________
Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Apr 2007
Posts: 8
creat a new table,and insert the result of select * from A and select * from B
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Mar 2007
Location: 636f6d7075746572
Posts: 770
Personally I WOULDN'T use temporary tables. Very messy!
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,721
Yeah, temp tables are an overkill. A simple UNION operation creates a temp table on the fly anyways...
__________________
Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On