query troubles 1

[aliaga61]

i know this might seem like a simple question, but's it's been a little while since i used sql.

i am trying to combine three different tables

example:

table 1
(primary key) user history
(primary key) project id

table 2
(primary key) project id
project name

table 3
(primary key) project id
(primary key) user id

i want to list the project id, the corresponding project name, the user id that corresponds to the project id, and the user history that corresponds to the user id.

i tried:

select project_id, project_name
from table2
where project_id in
(select user_id
from table3
where project_id in
(select user_history
from table1))
order by project_id;

but recieved "no rows selected" even though there was matching information. is there a better way to handle this?

 

[crufty]

try:

select t1.project_id, t2.project_name, t3.user_id, t1.user_history
from table1 t1, table2 t3, table3 t3
where t1.project_id = t2.project_id
AND t1.project_id = t3.project_id

Based on the table structure you presented, the userhistory is tied to the project_id instead of the user_id

If you want the user_id to be tied to the user_history, your table1 should be:

Table1:
user_id
user_history

Table2:
project_id
project_name

Table3:
user_id
project_id