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_ent ry WHERE
ministry_directory.listing_type = 2 AND
ministry_directory_listing_categories.ministry_directory_cat egory_reference = 10 AND ministry_directory_listing_categories.ministry_directory_cat egory_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: SELECT query from two tables

> ministry_directory_listing_categories.ministry_directory_cat egory_reference = 10 AND
>
ministry_directory_listing_categories.ministry_directory_cat egory_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: 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_cat egory_reference = 10 AND
> >
> ministry_directory_listing_categories.ministry_directory_cat egory_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: 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_cat egory_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: 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 =3D mdlc.ministry_di=
rectory_entry=20
WHERE md.listing_type =3D 2=20
AND mdlc.ministry_directory_category_reference IN (10, 11)=20
ORDER BY ministry_directory.name ASC



Regards,

Bruce

>>> Ron Piggott 10/03/2008 10:33:13 a.m. >>>
I am wondering what is wrong with this syntax? =20

SELECT * FROM ministry_directory INNER JOIN
ministry_directory_listing_categories ON ministry_directory.entry =3D
ministry_directory_listing_categories.ministry_directory_ent ry WHERE
ministry_directory.listing_type =3D 2 AND
ministry_directory_listing_categories.ministry_directory_cat egory_reference=
=3D 10 AND ministry_directory_listing_categories.ministry_directory_cat ego=
ry_reference =3D 11 ORDER BY ministry_directory.name ASC

It produces 0 results. =20

In reality there is presently 1 record that should be found that has
listing_type =3D 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.) =20

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


--=20
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php=20



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: 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 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_ent ry WHERE
> ministry_directory.listing_type = 2 AND
> ministry_directory_listing_categories.ministry_directory_cat egory_reference = 10 AND ministry_directory_listing_categories.ministry_directory_cat egory_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