Is This Possible?

[supertrucker]

Here's my sql statement in english:

Select all from table Employees where values in field UserNum are not in the table Benefits' field UserNum.

Is there a valid way of writing a single statement like this, without doing multiple statements from my php code?Thanks in advance!ST

[justsomeguy]

SELECT * FROM Employees WHERE UserNum NOT IN (SELECT UserNum FROM Benefits)That will work with MySQL 5.

[aalbetski]

this should work as well

select e.* from employees e join benefits b on e.usernum = b.usernum

don't know about mysql, works in sql server

[justsomeguy]

That finds the opposite set, the set of IDs that exist in both places.

[aalbetski]

yes, you are correct, sorry for the misinformation. Next time I'll read it all the way through

[supertrucker]

If anything I learned two new ways of doing something. That first example looks perfect, JSG, thanks! As far as that second one could you elaborate on the e.*? Is that a form of wildcard? I've used, '%test%' before, but that form looks new to me, I'll have to look that one up.Thanks for all your help!

[supertrucker]

Just a follow up, that this code worked perfect, makes my life a lot easier now that I can easily search for "orphaned" records in my db!Thanks again!ST

[justsomeguy]

It's just an alias for the table name. select e.* from employees e join benefits b on e.usernum = b.usernumThe query sets "e" to be an alias for the employees table, and "b" to be an alias for the benefits table. That's just so that you don't have to type the full name everywhere. It probably depends on the DBMS, but I was told that you should use AS when using an alias. I heard that this query:select e.* from employees e join benefits b on e.usernum = b.usernummay require the entire employees and benefits tables to be copied to new temporary tables, but this one:select e.* from employees AS e join benefits AS b on e.usernum = b.usernumwill just use an alias, where it uses one name to refer to the existing table.