Обсуждение: Joins
Hi, I'm trying to get a list of all the rows in the Parts table, with the integer indexes of man_id, case_id, and desc_id, replaced with text from their respective tables. In the Parts table, any of man_id, case_id, and desc_id may be absent: Table1, Parts: part man_id case_id desc_id ------------------------------ text int int int Table2: Manufacturers: man_id manufacturer -------------------- int text Table3, Cases: case_id case ------------- int text Table4, Descriptions: desc_id description -------------------- int text Is this right? : SELECT part, manufacturer, case, description FROM parts LEFT OUTER JOIN manufacturers, cases, descriptions NATURAL
Russell Shaw wrote: > Hi, > I'm trying to get a list of all the rows in the Parts table, with > the integer indexes of man_id, case_id, and desc_id, replaced with text > from their respective tables. In the Parts table, any of man_id, case_id, > and desc_id may be absent: Something like that? :^) SELECT * FROM parts LEFT OUTER JOIN manufacturers USING (man_id) LEFT OUTER JOIN cases USING (case_id) LEFT OUTER JOIN descriptions USING (desc_id); [...] > Is this right? : > > SELECT part, manufacturer, case, description > FROM parts LEFT OUTER JOIN manufacturers, cases, descriptions > NATURAL Hm, don't know if that syntax is correct, but LEFT OUTER JOIN is indeed what you want. But I don't recomment NATURAL as there may be columns with the same name which you don't want to join on. I suggest always specify "USING (fieldname)" or "ON (tab1.col1=tab2.col2)". You can also specify whole subqueries in the ON (...) part. HTH -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de