mysql query difficulties

[Anon]

I am trying to perform a query that resolves two id numbers into the names associated with them, but both of those joins have to take place on the same table, and one must be a left join. The query as it stands (non-functioning as far as mysql is concerned, but syntatically correct as far as I know is),

SELECT ports.name, devices.name as dname, destdevices.name as destname FROM ports, devices, devices AS destdevices
INNER JOIN devices ON devices.did = ports.did
LEFT JOIN destdevices ON destdevices.did = ports.destid;

Now a similar thing can be done with WHERE, but that would be equivalent to 2 INNER JOIN statments, and I really think I need it to be a LEFT JOIN in the second case. Interestingly, the SELECT does work if I use a WHERE (the behavior is not what I want, but mysql performs the query), but does not even execute once JOIN is used (either LEFT or INNER). Anyway, I don't know what the problem is, but it has been sort of exasperating to look for examples and only find ones using WHERE. Thanks in advance for any help.

[[deleted]]

SELECT ports.name, devices.name as dname, destdevices.name as destname
FROM ports, devices, devices AS destdevices
INNER JOIN devices ON devices.did = ports.did
LEFT JOIN destdevices ON destdevices.did = ports.destid;

Wahow, mysql has every right to complain here. If you run that query, mysql will tell you that you have a 'non unique table alias devices', which is correct because you're doinf a join between ports, devices, devices AS destdevices, then you INNER JOIN the destdivices with devices again, and LEFT JOIN all that with destdevices (again)

I guess what you really mean is:

SELECT ports.name, devices.name as dname, destdevices.name as destname
FROM ports
INNER JOIN devices ON devices.did = ports.did
LEFT JOIN devices AS destdevices ON destdevices.did = ports.destid;

---

A forum, a FAQ, email notification, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

[Anon]

Thanks, I guess I was trying to alias in the FROM, because that is the syntax that I found for relating to the same table twice in the mysql docs. I thought I had tried aliasing all the places that I could, but evidently not. Thanks.

[[deleted]]

You can only alias in the FROM, but the FROM starts at 'FROM' and ends at 'WHERE', so the joins are also in the FROM. 🙂