SQL Query - SELECT from multiple tables

7thblue · #1 (**permalink**) 03-28-07, 14:42

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.

RedNeckGeek · #2 (**permalink**) 03-28-07, 14:54

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.

Peter.Vanroose · #3 (**permalink**) 03-29-07, 02:17

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

7thblue · #4 (**permalink**) 03-29-07, 10:24

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.

Peter.Vanroose · #5 (**permalink**) 03-30-07, 02:28

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

stolze · #6 (**permalink**) 03-31-07, 12:16

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 )

bo8888 · #7 (**permalink**) 04-11-07, 07:03

creat a new table,and insert the result of select * from A and select * from B

aschk · #8 (**permalink**) 04-12-07, 08:38

Personally I WOULDN'T use temporary tables. Very messy!

stolze · #9 (**permalink**) 04-12-07, 14:27

Yeah, temp tables are an overkill. A simple UNION operation creates a temp table on the fly anyways...

Internet Services

Web Development

Digital Marketing

Consumer Tech