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
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
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
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
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
[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] Multiple values in SELECT query
I like the "IN" keyword:
SELECT * FROM table WHERE name LIKE "ABC" AND listing_type IN ('1','2');
works kinda like PHP's "in_array"
-- matt
On Sun, Mar 9, 2008 at 1:34 PM, Ron Piggott <[EMAIL PROTECTED]>
wrote:
> What is the correct syntax for where the results may be 1 or 2? What
> have I done wrong?
>
> SELECT * FROM table WHERE name LIKE "ABC" AND listing_type = 1 or 2
>
> ???
>
> Ron
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
[PHP-DB] Multiple values in SELECT query
What is the correct syntax for where the results may be 1 or 2? What have I done wrong? SELECT * FROM table WHERE name LIKE "ABC" AND listing_type = 1 or 2 ??? Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
