Three-way join trouble in MySQL-CF 1

[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. Can any of you CFers using MySQL see my error? Any help is appreciated.

 

[imstillatwork]

try something like this, you need to include more details of what you are selecting:
SELECT
W02.system,
W02.id,

TID.project_name
TID.tids_id,

IE.ID,
IE.wsh_id,
IE.tids_id

FROM
wsh02 AS W02,
ie_wsh02_tid00 AS IE,
tids AS TID
WHERE
W02.id = IE.id
AND TID.tids_id = IE.wsh_id
AND IE.wsh_id = 1

 

[csteinhilber]

The SQL looks correct... even though a bit peculiar (if you're only looking for i.wsh_id of "1", wouldn't you also just be looking for t.tids_id of "1"... also, the fact that you're not relating t.tids_id to i.wsh_id instead of i.tids_id makes my eyes cross). But without seeing your actual data, it's difficult to know why it's not returning the result set you expect.

The best way to troubleshoot is start retracing some of the basics to find out where the problem is.

Start with:

<CFQUERY NAME=&quot;testquery&quot; ...>
select *
from wsh02 
</CFQUERY>

<CFDUMP var=&quot;#testquery#&quot;>

just to make sure everything's kosher with your connection and your table.

Then move to something like:

<CFQUERY NAME=&quot;testquery&quot; ...>
select *
from wsh02 w, ie_wsh02_tid00 i
where w.id = i.id
</CFQUERY>

<CFDUMP var=&quot;#testquery#&quot;>

and so on. Gradually start adding bits of your where clauses and your other table until you get to a point where the resultset is empty. You will then have a good idea where the problem is. Start adjusting that portion of the query until you begin to get results, and that will probably provide a key to how to fix it.

The golden rule of SQL is start wide open, and then begin constricting your query one filter at a time until you either have the resultset you wanted, or you've ceased to return any results. If you cease to return results, go back one step, rethink, and try again.

Hope it helps,
-Carl