Posted by Kenoli on 12/19/06 13:30

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 ...

[Back to original message]