Reputation: 169
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
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
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