Hello,

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

id is common index for the 3 tables

I would like to have this ideal result :

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:

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

this is the simplest and most efficient query

lay it out for display however you want using your application language or reporting system

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
,id
,name as fld
....

$val=mysql_query($query);
while ($myArr=mysql_fetch_array($val)) {
print($myArr['id'].";"$myArr['fld'].";"$myArr['rowtype]."\n");
}
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