select records from 1 tbl excluding those found in another

[ppetree]

I have 2 tables: Users and Log

I want to select ALL users from Users who are not in Log having already received a msg_id matching 'x'

What I have so far is this:

SELECT * FROM users AS USR WHERE USR.user_id=2 NOT IN (
   SELECT *
   FROM msg_log AS LOG
   WHERE LOG.msg_id = 8 AND LOG.user_id = USR.user_id)

What happens and is I get the following error:
#1241 - Operand should contain 1 column(s)

I know I'm missing something obvious here but I dont see it...

[smantscheff]

Where you compare a value or a column with the result of a subselect this subselect must have the same number of columns as those which you are comparing - one, as a rule. So you cannot use the * operator in your subselect.
Apart from that your WHERE clause contains an error. Presumably it should read:

SELECT * FROM users AS USR WHERE USR.user_id NOT IN (
   SELECT user_id
   FROM msg_log AS LOG
   WHERE LOG.msg_id = 8

)

[ppetree]

Bang on! Thanks! I never knew that about matching the number of columns but it makes sense!

This is actually an extension to a very complicated query but since it works this way, I feel pretty certain I can integrate it.

Thanks!

[smantscheff]

What you then also will not know is that you can compare multiple columns (= tupels) in a sub-select. The standard comparison is just a 1-tupel. Example:

select * from mytable where (column1, column2) in (select v1, v2 from myothertable);

[ppetree]

What you then also will not know is that you can compare multiple columns (= tupels) in a sub-select. The standard comparison is just a 1-tupel. Example:

select * from mytable where (column1, column2) in (select v1, v2 from myothertable);

Thanks for that... I do much better when I see these things visually.