[PHP-DB] SELECT query from two tables
I am wondering what is wrong with this syntax? SELECT * FROM ministry_directory INNER JOIN ministry_directory_listing_categories ON ministry_directory.entry = ministry_directory_listing_categories.ministry_directory_entry WHERE ministry_directory.listing_type = 2 AND ministry_directory_listing_categories.ministry_directory_category_reference = 10 AND ministry_directory_listing_categories.ministry_directory_category_reference = 11 ORDER BY ministry_directory.name ASC It produces 0 results. In reality there is presently 1 record that should be found that has listing_type = 2 and ministry_directory_category_reference 10 and 11 in the ministry_directory_listing_categories table The table ministry_directory has the main contact information. entry is auto_increment; listing_type is an INT(1) column The table ministry_directory_listing_categories has 3 columns: reference which is auto_increment populated; ministry_directory_entry which is the common field between both tables, showing what the record belongs to ministry_directory_category_reference which is the reference number to how the directory listing was inputted / categorized. (IE If the person who completed the form select 2 of the 10 possible categories 2 records were created.) Is there a different way to word my query so I will be able to retrieve the record with two rows in table ministry_directory_listing_categories and 1 row in ministry_directory ? Thanks for the help guys. Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT query from two tables
ministry_directory_listing_categories.ministry_directory_category_reference = 10 AND ministry_directory_listing_categories.ministry_directory_category_reference = 11 Can a record really have a reference for two different id's like this? ie can it be both '10' and '11' at the same time? What's actually in the table for ministry_directory_listing_categories for this record? For long table names, I'd also suggest using a table alias to make it easier to read/write: select * from table1 as a inner join table2 as b using(id) where a.field_name='1' and b.fieldname='5'; saves you having to write out 'ministry_directory_listing_categories' and 'ministry_directory'. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT query from two tables
Two different rows Chris. reference ministry_directory_entry ministry_directory_category_reference 13 1 10 14 1 11 What I am trying to do is allow the user to make a more specific search. Ron On Mon, 2008-03-10 at 10:37 +1100, Chris wrote: ministry_directory_listing_categories.ministry_directory_category_reference = 10 AND ministry_directory_listing_categories.ministry_directory_category_reference = 11 Can a record really have a reference for two different id's like this? ie can it be both '10' and '11' at the same time? What's actually in the table for ministry_directory_listing_categories for this record? For long table names, I'd also suggest using a table alias to make it easier to read/write: select * from table1 as a inner join table2 as b using(id) where a.field_name='1' and b.fieldname='5'; saves you having to write out 'ministry_directory_listing_categories' and 'ministry_directory'. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT query from two tables
Ron Piggott wrote: Two different rows Chris. That's the problem then. Your query is saying get records with category_reference of 10 and it has to have category_reference of 11 as well. No such rows exist. Maybe that should be an 'or' or 'in' (same thing). ... ministry_directory_listing_categories.ministry_directory_category_reference in (10,11) ... ; so it can get both records 13 14. reference ministry_directory_entry ministry_directory_category_reference 13 1 10 14 1 11 -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT query from two tables
I think what you mean to do is use IN(). And I would suggest table aliases. So it could look like this: SELECT * FROM ministry_directory md INNER JOIN ministry_directory_listing_categories mdlc ON md.entry = mdlc.ministry_directory_entry WHERE md.listing_type = 2 AND mdlc.ministry_directory_category_reference IN (10, 11) ORDER BY ministry_directory.name ASC Regards, Bruce Ron Piggott [EMAIL PROTECTED] 10/03/2008 10:33:13 a.m. I am wondering what is wrong with this syntax? SELECT * FROM ministry_directory INNER JOIN ministry_directory_listing_categories ON ministry_directory.entry = ministry_directory_listing_categories.ministry_directory_entry WHERE ministry_directory.listing_type = 2 AND ministry_directory_listing_categories.ministry_directory_category_reference = 10 AND ministry_directory_listing_categories.ministry_directory_category_reference = 11 ORDER BY ministry_directory.name ASC It produces 0 results. In reality there is presently 1 record that should be found that has listing_type = 2 and ministry_directory_category_reference 10 and 11 in the ministry_directory_listing_categories table The table ministry_directory has the main contact information. entry is auto_increment; listing_type is an INT(1) column The table ministry_directory_listing_categories has 3 columns: reference which is auto_increment populated; ministry_directory_entry which is the common field between both tables, showing what the record belongs to ministry_directory_category_reference which is the reference number to how the directory listing was inputted / categorized. (IE If the person who completed the form select 2 of the 10 possible categories 2 records were created.) Is there a different way to word my query so I will be able to retrieve the record with two rows in table ministry_directory_listing_categories and 1 row in ministry_directory ? Thanks for the help guys. Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT query from two tables
Thanks On Mon, 2008-03-10 at 12:56 +1300, Bruce Cowin wrote: I think what you mean to do is use IN(). And I would suggest table aliases. So it could look like this: SELECT * FROM ministry_directory md INNER JOIN ministry_directory_listing_categories mdlc ON md.entry = mdlc.ministry_directory_entry WHERE md.listing_type = 2 AND mdlc.ministry_directory_category_reference IN (10, 11) ORDER BY ministry_directory.name ASC Regards, Bruce Ron Piggott [EMAIL PROTECTED] 10/03/2008 10:33:13 a.m. I am wondering what is wrong with this syntax? SELECT * FROM ministry_directory INNER JOIN ministry_directory_listing_categories ON ministry_directory.entry = ministry_directory_listing_categories.ministry_directory_entry WHERE ministry_directory.listing_type = 2 AND ministry_directory_listing_categories.ministry_directory_category_reference = 10 AND ministry_directory_listing_categories.ministry_directory_category_reference = 11 ORDER BY ministry_directory.name ASC It produces 0 results. In reality there is presently 1 record that should be found that has listing_type = 2 and ministry_directory_category_reference 10 and 11 in the ministry_directory_listing_categories table The table ministry_directory has the main contact information. entry is auto_increment; listing_type is an INT(1) column The table ministry_directory_listing_categories has 3 columns: reference which is auto_increment populated; ministry_directory_entry which is the common field between both tables, showing what the record belongs to ministry_directory_category_reference which is the reference number to how the directory listing was inputted / categorized. (IE If the person who completed the form select 2 of the 10 possible categories 2 records were created.) Is there a different way to word my query so I will be able to retrieve the record with two rows in table ministry_directory_listing_categories and 1 row in ministry_directory ? Thanks for the help guys. Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
