BlameThePixel!

calling all SQL programmers: need a query which will return all rows in table a that are not also in table b. I assume a join is needed, but I suck at joins If you'd like to help then um... i can give you $!

I suppose if I looked hard enough I could figure it out myself, but that'd be time consuming

Thanks in advance.

BTW, if multible queries are needed, post them all. Thx.

I suppose this is to solve the table breaking problem? Alright, I've got a few people I can ask on another forum, I'll get back to you with any news.

I may not know much about mysql but I do know a bit of the sql itself. Using EXCEPT in the following way will take all rows from table_a and subtract any that have a match in table_b. Replace the *s with whatever
columns you are checking for (eg which names appear in table_a and not table_b), just ensure that both select statements return the same number of columns.

SELECT * FROM table_a
EXCEPT
SELECT * FROM table_b

You might need to do a bit more to then get the entire rows for the particular names or ids that appear in table_a and not table_b, so I imagine you might wanna do something like this (I don't trust myself 100% tho heh):

SELECT * FROM table_a WHERE table_a.ID IN
(
SELECT ID FROM table_a
EXCEPT
SELECT ID FROM table_b
)

maybe that gives you the idea even if it's not quite right, the important part is EXCEPT, which gives you the rows that appear in the first select but not in the second select (assuming mysql supports except... )

I get a feeling EXCEPT isn't in mysql... I've searched for it, but mysql isn't actually that good. There's no UNION or a number of other functions that I forget... Seeing as I don't know how to use them

If there's no union then it won't have except either.

http://www.mysql.com/doc/en/UNION.html

Can't find EXCEPT...

BTW, bloopy, mySQL can't do subselects either. the best it can do is create a temporary table, then use that in new queries.

Nevermind what I just said http://www.mysql.com/doc/en/Subqueries.html

ignore this:
Backup Statement is "INSERT INTO `users_backup` SELECT * FROM `users`"

Digging in mySQL docs are useful.

Query found:

SELECT users_backup. *
FROM users_backup
LEFT JOIN users ON users.id = users_backup.id
WHERE users.id IS NULL

okay, so it's there, just not in the versions that are most used...

and...

Actually we're in version 4.0.something, do a SELECT VERSION()

I dindn't notice that until afterward.

BTW, Zog, can you enable CREATE TEMPORARY TABLE permission for the site's db account? Be pretty convienet.

Also, you cant do CREATE TABLE _ LIKE _ which was annoying, took me awhile to figure out i had to do a CREATE TABLE _ INSERT _ WHERE 1=2

erm, no I don't think I can actually... unfortunately I haven't got that kind of control