Thanks, I just learn't a lot about SQL --- Rob
************************** Rob Cherry mailto:[EMAIL PROTECTED] +27 21 447 7440 Jam Warehouse RSA Smart Business Innovation http://www.jamwarehouse.com ************************** -----Original Message----- From: Diana Soares [mailto:[EMAIL PROTECTED] Sent: Friday, February 28, 2003 3:11 PM To: Rob Cc: [EMAIL PROTECTED] Subject: Re: left outer join Hi, You have one thing wrong in your query... You're using LEFT JOIN but then you use a condition over DFL in the where clause. That's why it doesn't give you the results expected from a LEFT JOIN. You should have a look in the manual to see how to use left joins. The query should by: SELECT DF.id, DFL.id FROM document_fields AS DF LEFT JOIN document_fields_link AS DFL ON DF.id = DFL.document_field_id WHERE DF.is_generic = 1 I removed the DFL.document_id = 37 because you don't mention it in your goals. But if you need that, you should do like: SELECT DF.id, DFL.id FROM document_fields AS DF LEFT JOIN document_fields_link AS DFL ON DF.id = DFL.document_field_id AND DFL.document_id = 37 WHERE DF.is_generic = 1 This will give you all from DF, the DFL which have document_id = 37 and the others DFL will be NULL. On Fri, 2003-02-28 at 12:33, Rob wrote: > Could someone please tell me what I'm doing wrong here? > > I have the following two tables > > a)document_fields > > +----+-----------+-----------+------------+------------+ > | id | name | data_type | is_generic | has_lookup | > +----+-----------+-----------+------------+------------+ > | 1 | Category | String | 1 | NULL | > | 2 | Keywords | String | 1 | NULL | > | 3 | Comments | String | 0 | NULL | > | 4 | Author(s) | String | 0 | NULL | > +----+-----------+-----------+------------+------------+ > > b)document_fields_link > > +----+-------------+-------------------+-----------+ > | id | document_id | document_field_id | value | > +----+-------------+-------------------+-----------+ > | 57 | 37 | 3 | | > | 58 | 37 | 4 | jklhkljmh | > +----+-------------+-------------------+-----------+ > > I'm running the following query > > SELECT DF.id, DFL.id > FROM document_fields AS DF LEFT JOIN document_fields_link AS DFL on DF.id = > DFL.document_field_id > WHERE DFL.document_id = 37 > AND DF.is_generic = 1 > > Basically what I want to achieve is this. I want to select all the document > fields that are > generic and IF the document has values for those fields I want to see those > to, otherwise > I want to see null values? > > I thought a left outer join worked as follows: Select all items on the left > table (document_fields) > and join them to all items on the right table (document_fields_link) for > corresponding values > exists, otherwise insert null. > > So I should always get all the values in the document_fields table and > sometimes values in the > document_fields_link table. > > But I don't get any results back. > > Any ideas? > > > > --- > Rob > > ************************** > Rob Cherry > mailto:[EMAIL PROTECTED] > +27 21 447 7440 > Jam Warehouse RSA > Smart Business Innovation > http://www.jamwarehouse.com > ************************** > -- Diana Soares --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
