is this possible in sql???

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

is this possible in sql???

Post 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....
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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%"
Post Reply