i have several different tables that only have one feild in common. i've been searching this oreilly book "managing and using mysq" (i'm getting the feeling it's not worth the paper it's written on) and neither type of join will return what i want: the usernames from the users table where certain feilds match what someone has submitted via a form
there's a user id column that matches up on all the tables
issue is: i tried a sample table and i got joins that return columns where a common table is the same among two tables, but not more than two (i get an empty set when i try that)
i tried it with where that column is equal and something else i equal to some variable and got the same issue.
what i want to do is match for certain items the user inputs, the items will only be on one of the tables, but they cross multiple tables. i only really want one item from the main table (the username) but i wanna search against multiple tables (which can be synched up by user id number)
is there a way in sql 3.23.56 to select the item in the main table where items in toher tables have certain values and the tables are matched up via uid, or do i have to do what i'm thinking where i search for the uid on each of the tables, then, in php , check to see which uids are in all the results, then find the usernames....
is this possible in sql???
Moderator: General Moderators
Yes, you should be able to. In your case I'm not sure about the code, but the following snippet might aid you. Tweak and test.
Code: Select all
SELECT
d_person.dispname,
d_title.title
FROM
d_person, d_title, s_first, s_last, t_person, l_first, l_last
WHERE
t_person.dperson_id = d_person.dperson_id AND
t_person.dtitle_id = d_title.dtitle_id AND
l_first.fname_id = s_first.fname_id AND
l_last.lname_id = s_last.lname_id AND
t_person.person_id = l_first.person_id AND
t_person.person_id = l_last.person_id AND
s_first.first = "roger" AND
s_last.last LIKE "bakl%"