[Resolved] Please help

romic

Please help me to make a Select from My SQL database.
I need to select the ID and email of the Users that has no posts from the "users" table.
How to make the select from 2 tables (posts, users) if I have in the "posts" table a column IDUser which contains (for each post) the ID of the user from the "users" table?

Thanks.

mourisj

select IDUser from users
minus select IDUser from posts

romic

Originally posted by mourisj
select IDUser from users
minus select IDUser from posts

Thanks.
This should show me the ID of the users that don't have posts?

mourisj

Yes, it should.

However, you can have the email directly only if it is included in your posts table, which you should avoid .

You have to loop through your result set and do single lookup s with the IDs.

Or , if your version of MySql supports sub-selects, you can do:

select IDUser, email from users
where IDuser in (
select IDUser from users
minus
select IDUser from posts )

(I don't know if any version of MySql is able to do this).

romic

The word "minus" doesn't seem to be a reserved word, that's why the solution you gave me doesn't work.

In what version of MySQL "minus" works?

mourisj

... but I notice right now that minus does not work in my databases, too! I'm used to work a lot with "minus" on an Oracle DB, but I was not really aware that it is not available in MySql!.

Subselects don't work either in my installed version (4.0.18), so I have no quick solution at hand right now.

Maybe, I would try to use a temporary table, but before recommending a solution again, I will have to test.

I will do this later this evening.
C.U.

JJ Mouris

Roger_Ramjet

Simple LEFT JOIN to do this.

From the manual

If there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find records in a table that have no counterpart in another table:

mysql> SELECT table1.* FROM table1
-> LEFT JOIN table2 ON table1.id=table2.id
-> WHERE table2.id IS NULL;

This example finds all rows in table1 with an id value that is not present in table2 (that is, all rows in table1 with no corresponding row in table2). This assumes that table2.id is declared NOT NULL.

romic

Thanks Roger,
I will try this.

romic

Thanks again Roger, it works!