user2164882 user2164882 - 7 months ago 9
SQL Question

Combining 3 SQL queries into 1

Coming from a row in table A I need to get row in table B that gets a row in table C.
With my basic knowledge I put a query in a query in a query, but I assume there is a better way to do that?

<?php $result = pg_query($dbconn, "
SELECT sequence, w_homepage_package_id
FROM adempiere.w_homepage_package
WHERE sequence > 0
ORDER BY sequence ASC
"); // first QUERY
while ($promo = pg_fetch_row($result)) { // first WHILE
$id = $promo[1];
$produktfoto = pg_query($dbconn, "
SELECT w_bilder_id
FROM adempiere.w_homepage_image
WHERE w_homepage_package_id = $id LIMIT 1"); // second QUERY
while ($produktrow = pg_fetch_row($produktfoto)) { // second WHILE
$bilderid = $produktrow[0];
$bildurl = pg_query($dbconn, "
SELECT w_bildurl
FROM adempiere.w_bilder
WHERE w_bilder_id = $bilderid LIMIT 1"); // third QUERY
while ($bildpfad = pg_fetch_row($bildurl)) { // third WHILE
echo $bildpfad[0];
}
}
;}
?>

Answer

This should be the correct left join query (If I haven't forgotten anything)

SELECT * 
FROM  adempiere.w_homepage_package 
      LEFT JOIN adempiere.w_homepage_image 
             ON adempiere.w_homepage_package.w_homepage_package_id =  adempiere.w_homepage_image.w_homepage_package_id 
      LEFT JOIN adempiere.w_bilder 
             ON adempiere.w_homepage_image.w_bilder_id = adempiere.w_bilder.w_bilder_id 
WHERE  sequence > 0 
ORDER  BY sequence ASC 

To understand JOIN think about product cartesian:

Table1
f1  f2
----------
a   1
b   4
c   5

Table2
f3  f4
----------
a   aa
b   cc

f1  f2  f3  f4
--------------------------
a   1   a   aa
a   1   b   cc
b   4   a   aa
b   4   b   cc
c   5   

Table1 LEFT JOIN Table2 ON f1 = f3

a   1   a   aa
b   4   b   cc
c   5

Table1 INNER JOIN Table2 ON f1 = f3
a   1   a   aa
b   4   b   cc

There are a good example at wikypedia http://en.wikipedia.org/wiki/Join_(SQL)

Comments