MySQL Query Vprašanje

SELECT weblife.bwResorts.Id, weblife.bwResorts.Name
FROM weblife.bwResorts JOIN weblife.rlResortsHaveFacilities USING (weblife.rlResortsHaveFacilities.IdResort)
                       JOIN weblife.bwFacilities USING (weblife.rlResortsHaveFacilities.IdFacility)
ORDER BY weblife.bwFacilities.Id;

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.' at line 2

overlord_tm je 8. dec 2010 ob 09:51 izjavil:

SELECT f.* FROM bwFacilities f WHERE f.Id IN
 (SELECT rhf.IdFacility FROM rlResortsHaveFacilities rhf
 WHERE rhf.IdResort = @tvojId)

ali

SELECT f.* FROM bwFacilities f
 JOIN rlResortsHaveFacilities rhf ON f.Id = rhf.IdFacility
 WHERE rhf.IdResort = @tvojId

ali

SELECT f.* FROM bwFacilities f 
 JOIN rlResortsHaveFacilities rhf ON f.Id = rhf.IdFacility
 JOIN bwResorts r ON r.Id = rhf.IdResort
 WHERE rhf.IdResort = @tvojId

Prvi je nested querry, drugi je z joinom, tretji je tudi z joinom in malo nepotrebnim joinom (razen ce bos hkrati potreboval tudi podatke iz relacije resorts. Torej preizkusi prva dva querrya, drugi je bolj univerzalen, saj ga lahko uporabis tudi za kake druge namene. Hkrati pa iz tretjega lahko naredis view, kar bo programerjem olajsalo delo.

USING pa ne dela ker AFAIK je misljen kot nek poseben primer natural joina, za natural join je pa pogoj da imajo stolpci, po katerih se joina enako ime v shemah obeh relacij.

The USING(column_list) clause names a list of columns that must exist in both tables. If tables a and b both contain columns c1, c2, and c3, the following join compares corresponding columns from the two tables:

PS: Resno zacni uporabljat aliase ;) Pa lahko si malo bolj vljuden, saj ti isces pomoc. Ni ti treba cele tabele izdati, samo smiselen primer potrebujemo. Ampak tudi ce izdas, nebi smela biti kriza, v kolikor bo aplikacija kvalitetno narejena :) Ta zadnji query, ki si ga nalimal je narobe, napisal si za zelis videti katere facilitiese ima resort z izbranim id-jem, query pa izbere resorte, ki imajo dolocen facility. Sicer pa je tole one to many referenca pomoje, ker en facility lahko pripada samo enemu resortu, resort pa ima lahko vec faicilityev verjetno.

 $resultResortFacilities = mysql_query(sprintf("SELECT f.Id, f.Name,
                                         f.Address
                                         FROM weblife.bwFacilities f
                                         JOIN weblife.rlResortsHaveFacilities rhf ON f.Id=rhf.IdFacility
                                         JOIN weblife.bwResorts r ON r.Id=rhf.IdResort
                                         WHERE rhf.IdResort = '%s'",mysql_real_escape_string($rowResorts[0])))
                            or die('Could not execute Resort Facilties Query ['.mysql_error().']');

$q = sprintf("SELECT f.Id, f.Name, f.Address
                                        FROM weblife.bwFacilities f
                                        JOIN weblife.rlResortsHaveFacilities rhf ON f.Id=rhf.IdFacility
                                        JOIN weblife.bwResorts r ON r.Id=rhf.IdResort
                                        WHERE rhf.IdResort = %s", mysql_real_escape_string($rowResorts[0]));
$resultResortFacilities = mysql_query($q) or die('Could not execute Resort Facilties Query ['.mysql_error().']');

$q = sprintf("SELECT f.Id, f.Name, f.Address
                                        FROM weblife.bwFacilities f
                                        JOIN weblife.rlResortsHaveFacilities rhf ON f.Id=rhf.IdFacility
                                        JOIN weblife.bwResorts r ON r.Id=rhf.IdResort
                                        WHERE rhf.IdResort = $rowResorts[0]");
$resultResortFacilities = mysql_query($q) or die('Could not execute Resort Facilties Query ['.mysql_error().']');