MySQL Help, Please

[[Admin] danroth]

okay, I'm still not good with multi-table MySQL commands
(which, of course, is how all the really cool stuff gets done)

Can someone please help with a query to do the following:

Select table1.name where [table1.name is not in table2.name] and [table1.name is not in table3.name]

TIA!

[hussra]

I get to work with MS SQL Server rather than MySQL, but you want something along the lines of:

select table1.name from table1
where table1.name not in (select table2.name from table2)
and table1.name not in (select table3.name from table3)

See http://dev.mysql.com/doc/refman/5.0/...ubqueries.html and http://dev.mysql.com/doc/refman/5.0/...ubqueries.html.

[[Admin] danroth]

Yes, I want something along those lines.

[geezer]I was hoping for one of those "INNER JOIN"s or something else like the kids use nowadays.[/geezer]

[hussra]

Originally Posted by danroth

[geezer]I was hoping for one of those "INNER JOIN"s or something else like the kids use nowadays.[/geezer]

I'm not really thinking straight this afternoon but I think you want to do a left outer join with table2 and then "where table2.name is null"? Then ditto with table3.

Something vaguely like this?

select table1.name from table1
left outer join table2 on table1.name = table2.name
left outer join table3 on table1.name = table3.name
where table2.name is null
and table3.name is null

I need a cup of tea now!

[waynehoskins]

Yep, the left join on the null condition is the way to do it.

"outer" is optional; by default a left join is a left outer join.

You're looking to, for example, return the list of people who own property in Dallas County (table1) but do not own property in either Collin or Tarrant counties, something like that?

[[Admin] danroth]

Originally Posted by waynehoskins

You're looking to, for example, return the list of people who own property in Dallas County (table1) but do not own property in either Collin or Tarrant counties, something like that?

that's correct

[[Admin] danroth]

okay, to throw a wrench in it (feel free to tell me to RTM) -
what if one of those tables is in a different database?

[hussra]

Originally Posted by danroth

okay, to throw a wrench in it (feel free to tell me to RTM) -
what if one of those tables is in a different database?

Assuming the different database is at least on the same server... you can then just use db_name.table_name and db_name.table_name.column_name in place of table_name and table_name.column_name.

[[Admin] danroth]

yeah, they are same server


Thanks everybody, you've been a big help!!!

[waynehoskins]

See, occasionally we are useful!

Glad to help.