Results 1 to 3 of 3

Thread: List 3 tables

  1. #1
    Join Date
    Nov 2009

    Unanswered: List 3 tables


    Problem to list 3 tables side by side in one resulting table.
    My tables look like these :

    HTML Code:
    table a   table b   table c
    ------   --------  -------
    id name  id pets  id cars
    0 name1   0 pet1   0 car1
    1 name2   0 pet2   0 car2
              1 pet3   0 car3
              1 pet4   1 car4
    id is common index for the 3 tables

    I would like to have this ideal result :

    HTML Code:
    id  name   pets  cars
    0   name1  pet1  car1
    .     .    pet2  car2
    .     .      .   car3
    1   name2  pet3  . 
    .     .    pet4  car4
    I know empty cells are not easy to get, so i'll be happy with this result
    id name pets cars
    0 name1 pet1 car1
    0 name1 pet2 car2
    0 name1 pet1(or 2) car3
    1 name2 pet3 car4
    1 name2 pet4 car4

    When I do a test on 3 tables with this classic SQL string:

    HTML Code:
    SELECT,, p.pets, FROM table1 n 
       INNER JOIN table2 p ON = 
       INNER JOIN table3 c ON =
    I got the cartesian product of table1, table2 and table3:

    id name pets cars
    0 name1 pet1 car1
    0 name1 pet1 car2
    0 name1 pet1 car3
    0 name1 pet2 car1
    0 name1 pet2 car2
    0 name1 pet2 car3
    1 name2 pet3 car4
    1 name2 pet4 car4

    So I have 8 rows instead of 5 because 8 is sum of
    1 * 2 * 3 choices for name1
    1 * 2 * 1 choices for name2

    Of course my question is how to code SQL query. I tried INNER OUTER JOINS
    UNION but didn't get what I wish.
    Thanks for any help

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    SELECT 'a'    AS rowtype
         , id
         , name   AS fld
      FROM table_a
    SELECT 'b'    
         , id
         , pets   
      FROM table_b
    SELECT 'c'    
         , id
         , cars   
      FROM table_c
        BY id
         , rowtype
    this is the simplest and most efficient query

    lay it out for display however you want using your application language or reporting system | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2009
    perfect, perfect, thanks.

    I saw that UNION could mix two tables in a 2 columns table, but I didn't see how to know which field comes from which table. Adding the rowtype marker is the trick.
    First I got a table with 2 columns and right number of rows, then realised I had to display the rowtype value in a 3rd column to display the marker.
    Finally, I just had to add ORDER BY id, rowtype, fld
    So PHP code is:
    $query= "SELECT 'a' as rowtype
    ,name as fld

    while ($myArr=mysql_fetch_array($val)) {
    the following the result looks like
    id fld rowtype
    0 ; name1 ; a
    0 ; pet1 ; b

    Now UNION is clear for me (I hope)
    Thanks again
    Last edited by smr; 11-16-09 at 04:16.

Tags for this Thread

Posting Permissions

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