|
Posted by Captain Paralytic on 12/18/06 13:19
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 ...
Navigation:
[Reply to this message]
|