CODEWITHSUNDEEP
mysqlsql
Kimomaru
Kimomaru

Reputation: 1023

Omiting entries with a subquery

I'm having trouble understanding how to use a subquery to remove entries from a main query. I have two tables;

mysql> select userid, username, firstname, lastname from users_accounts where (userid = 7) or (userid = 8);

+--------+----------+-----------+----------+
| userid | username | firstname | lastname |
+--------+----------+-----------+----------+
|      7 | csmith   | Chris     | Smith    |
|      8 | dsmith   | Dan       | Smith    |
+--------+----------+-----------+----------+
2 rows in set (0.00 sec)

mysql> select * from users_contacts where (userid = 7) or (userid = 8);
+---------+--------+-----------+-----------+---------------------+
| tableid | userid | contactid | confirmed | timestamp           |
+---------+--------+-----------+-----------+---------------------+
|       4 |      7 |         7 |         0 | 2013-10-03 12:34:24 |
|       6 |      8 |         8 |         0 | 2013-10-04 09:05:00 |
|       7 |      7 |         8 |         1 | 2013-10-04 09:08:20 |
+---------+--------+-----------+-----------+---------------------+
3 rows in set (0.00 sec)

What I would like to do is pull a list of contacts from the users_accounts table that will;

1) Omit the user's own account (in other words, I don't want to see my own name in the list).

2) See all contacts that have a "confirmed" state of "0", but

3) If the contact also happens to have a "confirmed" status of "1" (request sent) or "2" (request confirmed), do not include them in the results.

How can a sub-query be written to pull anything that turns up as a 1 or 2?

Upvotes: 3

Views: 54

Answers (1)

Kai Qing
Kai Qing

Reputation: 18833

Subqueries at this point do not look necessary. You could join the tables like so:

select u.userid, u., firstname, u.lastname from users_accounts u join user_contacts c on u.userid = c.userid where u.userid != your_user_id and c.confirmed = 0;

in this generic example, your_user_id is obviously a placeholder for however you determine the current user's id. but if you absolutely must use a subquery:

select userid, username, firstname, lastname from users_accounts where userid != your_user_id and userid not in (select userid from user_contacts where confirmed = 1 or confirmed = 2);

Upvotes: 2

Related Questions