SQL Help

[lensman]

Anyone want to try to help me with this. I have a DB with 2 tables (users and instance) a bunch of records were deleted from the users table, and I want to find record id numbers and a record name from the instance table that have userid's that have been deleted and that are of a type "Catagory".... I've got most of the statement working, but the "userid not in users.userid" doesn't because I'm not sure how to refer to the column as a list....

SELECT t1.userid, t1.iid, t1.name FROM instance t1, users t2 WHERE ( ??? ) AND ( t1.IsA = "Category" ) ORDER BY t1.userid ASC;

Comments

[developer.livejournal.com]

There are a couple of options. Likely the most elegant solution is some kind of outer join, however I'm feeling inelegant so I will throw out there the 'not in' statement.

I think you want something like:

SELECT t1.userid, t1.iid, t1.name FROM instance t1
WHERE t1.userid NOT IN (select userid from users)
AND ( t1.IsA = "Category" )
ORDER BY t1.userid ASC;

[siderea]

Likely the most elegant solution is some kind of outer join

SELECT i.userid, i.iid, i.name
FROM instance i
LEFT JOIN users u
ON i.userid=u.id
WHERE u.id IS NULL and i.IsA='Category'

BTW, you will burn in hell for using the aliases "t1" and "t2".

[developer.livejournal.com]

t1 and t2 weren't my idea....

[lensman.livejournal.com]

"BTW, you will burn in hell for using the aliases "t1" and "t2"."

Yeah I'll be burning in hell for SOO many other reasons, that coding style points will have to wait their turn. :-)

[awfief.livejournal.com]

Yeah, that's not going to play so well on MySQL, which I'm assuming is what's being used....

The correct statement is an outer join and not actually more work than the "not in".

SELECT t1.userid, t1.id, t1.name
FROM instance t1 LEFT OUTER JOIN users t2
USING (userid)
WHERE t2.userid IS NULL AND t1.IsA = "Category"
ORDER BY t1.userid ASC;

the LEFT OUTER JOIN takes everything in t1 and attempts to match it with a corresponding userid in t2 (the USING clause) -- if it doesn't match, t2.userid will be NULL.

Love your friendly neighborhood She-BA

[developer.livejournal.com]

The last time I prodded at MySQL it didn't support foreign keys which sort of meant it wasn't a SQL database in my mind. :P

No doubt yours is the better statement, although the 'using' might need to be an 'on' depending on the database.

[lensman.livejournal.com]

The inelegant syntax failed to execute, but the rest of the comment lead me to what I needed anyway... :-)
Some days you just stare at the problem too long...

[developer.livejournal.com]

Yeah, I suck at writing code I can't run. In the punch card days I'd have lasted roughly one week. :)