Page 1 of 1

is this possible in sql???

Posted: Wed Aug 27, 2003 10:05 pm
by m3rajk
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....

Posted: Wed Aug 27, 2003 11:52 pm
by JAM
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%"