Three-way join trouble

[RikForgo]

Hello all,

I'm new at this, so please be gentle. I'm using MySQL with ColdFusion, and need a way to display a list of data from one table when a user selects a particular system. I know I need to use a three-way join to get where I need to go, but I'm getting lost along the way. Here's my setup:

----------------------- ---------------------------- ------------------------
| wsh02 | | ie_wsh02_tid00 | | tids |
----------------------- ---------------------------- ------------------------
id (pk) id (pk) tids_id (pk)
system wsh_id project_name
tids_id

A simple queries runs and provides the user with a system from wsh02. From that page I need to be able to show all the related project names (from tids). I've tried the query below, but it keeps returning zero records (and I know there are 11 related records).

select system, w.id, project_name
from wsh02 w, tids t, ie_wsh02_tid00 i
where w.id = i.id
and t.tids_id = i.wsh_id
and i.wsh_id = 1;

I think I'm missing something simple, but I don't know where. Any help is appreciated.

 

[sleipnir214]

Just guessing from your column names, shouldn't your query be:

select system, w.id, project_name
from wsh02 w, tids t, ie_wsh02_tid00 i
where w.id = wsh.id
and t.tids_id = i.tids_id
and i.wsh_id = 1 ______________________________________________________________________
TANSTAAFL!

 

[RikForgo]

I'm still not having much luck with this. I'm hoping putting some of the data up will be useful. Here's what I've got.

WSH02
-------------------
system id
-------------------
Abrams 1
Hornet 53
Stinger 100

... and ...


TIDS
------------------------
proj_name id
------------------------
API 1389
TTY 1102
ATC 1391
EHTL 1402
HHR 989
JFST 1417
MDCSU 1424
JJK 1711

There may be multiple projects for a single system. To accommodate them, I created an intersecting table that bridges the two. I can't keep the multiple references to tids in the wsh02 table, nor can I keep them in the tids table. As best I can figure, I need to have a bridge table.


IE_WSH02_TID00
-------------------------------
id wsh_id tids_id
-------------------------------
1 1 1389
2 53 1102
3 1 1391
4 1 1402
5 53 989
6 1 1417
7 1 1424
8 100 1711

What I want is a query that will find all the TIDS entries that relate to a specific WSH02 row. For example, when I query on "Abrams" in the WSh02 table, I should find five matching rows in the TIDS table, and return a result like below:

q.Abrams
--------------------------------------------
system project_name tids_id
--------------------------------------------
Abrams API 1389
Abrams ATC 1391
Abrams EHTL 1402
Abrams JFST 1417
Abrams MDCSU 1424

But the query I thought would work returns zero rows ....

select w.system, w.id, t.project_name
from wsh02 w, tids t, ie_wsh02_tid00 i
where t.tids_id = i.tids_id
and w.id = i.wsh_id
and w.id = '1';

I am missing something somewhere ... I don't know if the query is wrong, or if I have the table structure wrong, or what.