CODEWITHSUNDEEP
mysqljoin
jmd9qs
jmd9qs

Reputation: 169

MySQL select records from one table if their id AND username do not appear in a second table

I have two tables like this:

logbook:
+------------+-------------+------+-----+-------------------+----------------+
| Field      | Type        | Null | Key | Default           | Extra          |
+------------+-------------+------+-----+-------------------+----------------+
| id         | int(11)     | NO   | PRI | NULL              | auto_increment |
| date_added | timestamp   | NO   |     | CURRENT_TIMESTAMP |                |
| username   | varchar(16) | NO   |     | NULL              |                |
| entry      | longtext    | NO   | MUL | NULL              |                |
+------------+-------------+------+-----+-------------------+----------------+

and 

read_logbook:
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| logbook_id | int(11)     | NO   |     | NULL    |                |
| username   | varchar(16) | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

What I'd like to do is to select EVERYTHING from logbook, but only if the logbook.id AND logbook.username DO NOT appear in read_logbook.logbook_id and read_logbook.username, respectively.

I've experimented with some left-union-right queries, as well as some not in queries, and keep getting either thousands more results than expected, or no results at all.

Any thoughts?

EDIT - I'd run this for a specific username... so basically, if my username was jmd9qs, I'd want all results from logbook where read_logbook.id != logbook.id and read_logbook.username != jmd9qs

I hope that's clear enough...

EDIT - TEST DATA

The logbook:

mysql> select id, date_added, username from logbook order by id desc limit 10;
+----+---------------------+-----------+
| id | date_added          | username  |
+----+---------------------+-----------+
| 94 | 2013-09-03 14:54:25 | tluce     |
| 93 | 2013-09-03 13:12:02 | tluce     |
| 92 | 2013-09-03 11:42:14 | tluce     |
| 91 | 2013-09-03 08:28:20 | jmd9qs    |
| 90 | 2013-09-03 07:13:36 | jmd9qs    |
| 89 | 2013-09-03 07:05:19 | jmd9qs    |
| 88 | 2013-09-03 06:57:47 | jsawtelle |
| 87 | 2013-09-03 06:15:42 | jsawtelle |
| 86 | 2013-09-03 05:21:14 | jsawtelle |
| 85 | 2013-09-03 03:52:25 | jsawtelle |
+----+---------------------+-----------+

Logbook entries that have been "marked" as read:

mysql> select logbook_id, username from read_logbook group by logbook_id desc limit 10;
+------------+----------+
| logbook_id | username |
+------------+----------+
|         94 | jmd9qs   |
|         93 | jmd9qs   |
|         92 | jmd9qs   |
|         91 | jmd9qs   |
|         90 | jmd9qs   |
|         89 | jmd9qs   |
|         88 | jmd9qs   |
|         87 | jmd9qs   |
|         86 | jmd9qs   |
|         85 | jmd9qs   |
+------------+----------+
10 rows in set (0.00 sec)

So when I run the query for jmd9qs, nothing should come up because in read_logbook, his username and the logbook id show up.

CLARIFICATION -

So in the logbook, the username is just the person who wrote logbook.entry. In read_logbook, username is the person who READ that entry. So if I'm logged in as jmd9qs, and I try to view the logbook, since I've read everything no logbook.entry's should come up. But for another user who HASN'T read that specific entry, the entry WOULD show up.

Upvotes: 0

Views: 161

Answers (2)

Andrew
Andrew

Reputation: 528

SELECT * 
FROM   logbook 
WHERE  logbook.username NOT IN 
    (SELECT read_logbook.username 
     FROM   read_logbook 
     WHERE  read_logbook.username='jmd9qs')
AND logbook.id NOT IN
    (SELECT read_logbook.logbook_id
     FROM   read_logbook);

Upvotes: 1

Marco
Marco

Reputation: 57603

If I understand your needs, you should try

SELECT t1.* FROM logbook t1
LEFT JOIN read_logbook t2
  ON t1.id = t2.id AND t1.username = t2.username
WHERE t2.id IS NULL AND t2.username IS NULL

Upvotes: 1

Related Questions