MySQL people, in here

[alocurto]

I have read up on the web but I could use a nice description of what I need to do to preform this. Or if it can even be done.

I have 4 tables: Marketers, Customers, FollowUp and PeopleInfo

They all have their own keys and fields but are linked by the userID column.

Now the thing is.... PeopleInfo has every userID and a full list of valid userIDs.
Marketers has only those users who are marketers.
Customers has only those users who are customers.
FollowUp has more generic questions that extend the PeopleInfo Table.

It is possible for the userID to only exsit in the PeopleInfo.
It is also possible for the userID to be in FollowUp and People Info.
It is also possible for it to be in Marketer, FollowUp and PeopleInfo OR Customers, FollowUp and PeopleInfo but never all 4 together.

Now what I would like to do is create a search where the person searching can select a field from a drop down (value passed as tableName.field) and enter a value then hit submit. Then MySQL would be able to get the desired field and throw me all the PeopleInfo for the found person. The search page would also have several search drop downs with AND/OR clauses.

For example:

the user selects Marketers.yearsOfMarketing = '4' OR Customers.yearsOfBuying = '3' ---> this would return the PeopleInfo of the userID stored in the respected table (Marketers or Customers). Make sense?

Should I Join, Select a lot or what?

-alocurto

 

[b4u]

My 2 cents:

You could try to make a huge select, with left joins so you can cross all tables, and apply the filtering you want ... that would be an easy way to do it, but depending of the records on the tables, it will be more performance intense.

For example:

SELECT PI.userID, PI.username
FROM PeopleInfo PI
left join Marketers MK on PI.userID=MK.userID
left join Customers CU on PI.userID=CU.userID
left join FollowUp FU on PI.userID=FU.userID
WHERE MK.yearsOfMarketing = '4' OR CU.yearsOfBuying = '3'
GROUP BY PI.userID, PI.username


The WHERE clause fields will be created dinamically (spell correct? ) on your code.

(I put some alias on tables to short the writing and understanding of the statement)

Result: the left join picks ALL records from the table at the left (in all this cases its the PeopleInfo, your main table which has all userID's) and crosses with the table at the right (in this case the 3 other tables). If for example a userID in PeopleInfo does NOT exist in Marketers, the fields in Marketers will be returned NULL, but still the fields of PeopleInfo return their proper value. (You can easily see this, by placing one field of each table as return fields, and remove the WHERE and GROUP BY clauses)


This is just a simple idea (if I understood what you want, that is).

 

[alocurto]

You are a life saver. Thanks alot!