Help with a lookup query

I am doing a compound lookup by deriving person_id values for a query
from various tables by selecting arrays of person_id values that meet
my criteria from those tables. I am ending up with a query whose logic
would be something like:

SELECT * FROM tbl_person WHERE first_name = "John" AND (person_id = '2'
OR person_id = '4' OR person_id = '6' OR person_id = '10') AND
(person_id = '1' OR person_id = '4' OR person_id = '10' OR person_id =
'20') AND (person_id = '4' OR person_id = '7' OR person_id = '6' OR
person_id = '10');

I have the id values from the lookup tables in separate arrays. I'm
thinking that the equivalent to the above query would be an array made
up only of values that show up in all three arrays. I could then
create a simpler query like:

SELECT * FROM tbl_person WHERE first_name = "John" AND (person_id = '4'
OR person_id = '10');

(The person_id values here are ones that show up in all three of the
parenthetical expressions in the first query.)

Does this make sense? Is there a php function that will create an
array from several arrays that is made up only of the values that show
up in all the arrays? If not, any idea how to do this?

Is there a simpler way entirely for approaching this entire issue?
Here is the setup:

I have three tables, each of which has criteria a person may search on
like:

tbl_person
person_id
name
region
member(y/n)

tbl_interest (this is a linking table so there may be several interest
areas associated with each person_id)
person_id
Interest_id

tbl_contact
person_id
contact_id
city
State
Country

>From a form people will enter values for the columns in these tables.
The way I am doing this now is to derive the person_id values from each
table associated with the lookup values from the form. Then I am
combining them into a final query I will use to select rows from the
person table as indicated above.

Any ideas for simplifyting this would be greatly appreciated.

--Kenoli

Re: Help with a lookup query

Kenoli wrote:
> I am doing a compound lookup by deriving person_id values for a query
> from various tables by selecting arrays of person_id values that meet
> my criteria from those tables. I am ending up with a query whose logic
> would be something like:
>
> SELECT * FROM tbl_person WHERE first_name = "John" AND (person_id = '2'
> OR person_id = '4' OR person_id = '6' OR person_id = '10') AND
> (person_id = '1' OR person_id = '4' OR person_id = '10' OR person_id =
> '20') AND (person_id = '4' OR person_id = '7' OR person_id = '6' OR
> person_id = '10');
>
> I have the id values from the lookup tables in separate arrays. I'm
> thinking that the equivalent to the above query would be an array made
> up only of values that show up in all three arrays. I could then
> create a simpler query like:
>
> SELECT * FROM tbl_person WHERE first_name = "John" AND (person_id = '4'
> OR person_id = '10');
>
> (The person_id values here are ones that show up in all three of the
> parenthetical expressions in the first query.)
>
> Does this make sense? Is there a php function that will create an
> array from several arrays that is made up only of the values that show
> up in all the arrays? If not, any idea how to do this?
>
> Is there a simpler way entirely for approaching this entire issue?
> Here is the setup:
>
> I have three tables, each of which has criteria a person may search on
> like:
>
> tbl_person
> person_id
> name
> region
> member(y/n)
>
> tbl_interest (this is a linking table so there may be several interest
> areas associated with each person_id)
> person_id
> Interest_id
>
> tbl_contact
> person_id
> contact_id
> city
> State
> Country
>
> >From a form people will enter values for the columns in these tables.
> The way I am doing this now is to derive the person_id values from each
> table associated with the lookup values from the form. Then I am
> combining them into a final query I will use to select rows from the
> person table as indicated above.
>
> Any ideas for simplifyting this would be greatly appreciated.
>
> --Kenoli

The way to do this is by using JOINs. You join all the tables on
person_id and then put all your "real" criteria in the WHERE clause.

It'll end up looking something like this:

SELECT
tbl_person.*
FROM tbl_person
JOIN tbl_interest USING(person_id)
JOIN tbl_contact USING(person_id)
WHERE
tbl_interest.Interest_id = 5
OR tbl_contact.State = 'AC'
.... and so on ...

Re: Help with a lookup query

Thanks. This points me in a useful direction.

Captain Paralytic wrote:
> Kenoli wrote:
> > I am doing a compound lookup by deriving person_id values for a query
> > from various tables by selecting arrays of person_id values that meet
> > my criteria from those tables. I am ending up with a query whose logic
> > would be something like:
> >
> > SELECT * FROM tbl_person WHERE first_name = "John" AND (person_id = '2'
> > OR person_id = '4' OR person_id = '6' OR person_id = '10') AND
> > (person_id = '1' OR person_id = '4' OR person_id = '10' OR person_id =
> > '20') AND (person_id = '4' OR person_id = '7' OR person_id = '6' OR
> > person_id = '10');
> >
> > I have the id values from the lookup tables in separate arrays. I'm
> > thinking that the equivalent to the above query would be an array made
> > up only of values that show up in all three arrays. I could then
> > create a simpler query like:
> >
> > SELECT * FROM tbl_person WHERE first_name = "John" AND (person_id = '4'
> > OR person_id = '10');
> >
> > (The person_id values here are ones that show up in all three of the
> > parenthetical expressions in the first query.)
> >
> > Does this make sense? Is there a php function that will create an
> > array from several arrays that is made up only of the values that show
> > up in all the arrays? If not, any idea how to do this?
> >
> > Is there a simpler way entirely for approaching this entire issue?
> > Here is the setup:
> >
> > I have three tables, each of which has criteria a person may search on
> > like:
> >
> > tbl_person
> > person_id
> > name
> > region
> > member(y/n)
> >
> > tbl_interest (this is a linking table so there may be several interest
> > areas associated with each person_id)
> > person_id
> > Interest_id
> >
> > tbl_contact
> > person_id
> > contact_id
> > city
> > State
> > Country
> >
> > >From a form people will enter values for the columns in these tables.
> > The way I am doing this now is to derive the person_id values from each
> > table associated with the lookup values from the form. Then I am
> > combining them into a final query I will use to select rows from the
> > person table as indicated above.
> >
> > Any ideas for simplifyting this would be greatly appreciated.
> >
> > --Kenoli
>
> The way to do this is by using JOINs. You join all the tables on
> person_id and then put all your "real" criteria in the WHERE clause.
>
> It'll end up looking something like this:
>
> SELECT
> tbl_person.*
> FROM tbl_person
> JOIN tbl_interest USING(person_id)
> JOIN tbl_contact USING(person_id)
> WHERE
> tbl_interest.Interest_id = 5
> OR tbl_contact.State = 'AC'
> ... and so on ...