SQL Joins etc

[intellix]

Having problems with my SQL query, which seems to work fine until I try to add another table

SELECT a.u_id, a.u_name, a.team_id, b.team_id, b.team_tag, count(c.u_id), SUM(c.city_pop)
FROM user a LEFT JOIN team b ON (a.team_id = b.team_id), city c
WHERE c.u_id IS NOT NULL
AND a.u_id = c.u_id
AND a.u_id <> '1'
GROUP BY a.u_id
ORDER BY SUM(c.city_pop) DESC

But lets say I want to find another field... I add another count(d.u_id) and a.u_id = d.u_id (which I would thought just uses the ID and grabs what you need)

The results completely mess up, does something weird with the results which are totally bogus.

SELECT a.u_id, a.u_name, a.team_id, b.team_id, b.team_tag, count(c.u_id), SUM(c.city_pop), count(d.u_id)
FROM user a LEFT JOIN team b ON (a.team_id = b.team_id), city c, exp d
WHERE c.u_id IS NOT NULL
AND a.u_id = c.u_id
AND a.u_id = d.u_id
AND a.u_id <> '1'
GROUP BY a.u_id
ORDER BY SUM(c.city_pop) DESC

This also doesn't work:

SELECT a.u_id, a.u_name, a.team_id, b.team_id, b.team_tag, count(c.u_id), SUM(c.city_pop), count(d.u_id)
FROM user a LEFT JOIN team b ON (a.team_id = b.team_id) LEFT JOIN exp d ON (a.u_id = d.u_id), city c
WHERE c.u_id IS NOT NULL
AND a.u_id = c.u_id
AND a.u_id <> '1'
GROUP BY a.u_id
ORDER BY SUM(c.city_pop) DESC

Thanks, if anyone can help :/

 

[cjmUK]

It would be a helpful if you could script some representative DDL/DML to allow us to replicate the problem... You haven't explained what the 'messed up' results are.

Also I prefer to be more explicit with my joins:

Select *
From ....
Inner Join ... On...
Inner Join ... On ...
etc

OK it's slightly more effort to write, but IMHO it's easier to read, and debug - - though I accept it's very subjective.

 

[lokkers]

I'd also use aliases that are more intuitive as to the tables you are querying. User u and Team t for example I would find much easier to read than User a and Team b.

Whilst that doesn't help with this particular problem, it'll make your SQL easier to read (in my experience anyway).