Results 1 to 9 of 9
  1. #1
    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.

  2. #2
    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

  3. #3
    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/

  4. #4
    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.

  5. #5
    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/

  6. #6
    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, ...
    ... 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

  7. #7
    Join Date
    Apr 2007
    Posts
    8
    creat a new table,and insert the result of select * from A and select * from B

  8. #8
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Personally I WOULDN'T use temporary tables. Very messy!

  9. #9
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •