SQL Query

[SecretShop]

Im having alot of trouble creating an SQL query to select the data that I need to view.  I have 6 relevant tables in this database: clients, users, wildcards, groups, users_groups, and vouchers.  Now what I want to do is select 1 row that contains information from all those tables.  The problem I'm having is that if theres not matching data in a table it will always return an Empty set.  Heres a really simple version of my query:

Code Select Expand


SELECT u.flags as uf, g.flags as gf, w.flags as wf, c.flags as cf \
FROM users u, groups g, users_groups ug, wildcards w, clients c \
WHERE u.username='secretshop' AND (ug.username='secretshop' AND g.groupname=ug.groupname) AND 'secretshop' LIKE w.pattern AND c.client='WAR3';


This works unless theres no matching client or pattern or users_groups association or username.  I have this query semi working with LEFt JOIN statements as show below:

Code Select Expand



SELECT u.flags as uf, g.flags as gf, w.flags as wf, c.flags as cf \
FROM users as u \
LEFT JOIN (groups as g, users_groups as ug) ON(ug.username='secretshop' AND g.groupname=ug.groupname) \
LEFT JOIN clients as c ON(c.client='WAR3') \
LEFT JOIN wildcards as w ON('secretshop' LIKE w.pattern)
WHERE u.username='secretshop';

However this too will return nothing if there is no user entry. Using mass joins also causes a performance hit that Id rather not take, but will obviously if its required.

If anyone has any advise on this query and how I can get it to do what I want, please post.  Also It has occured to me that Im going about it completly wrong so if anyone wants to suggest a different approach that would be great aswell.

[rabbit]

LEFT JOIN should never be used with WHERE.  Try UNION.

[SecretShop]

I had UNIONs in a previous encarnation of this query, however it seemed overly large and inefficient.  Why is it bad to use LEFT JOINs this way?  Im not all that familiar with SQL optimization yet.

[rabbit]

Because JOINs are replacements for WHEREs.  They are conditional checks just the same.  Having both can confuse your SQL server.