问题描述:

I have 3 tables like these

news_table:

newsID int auto_increment,

title varchar(256),

records are like:

(1, 'some_title' ),

(2, 'some_title' ),

(3, 'some_title' ),

(4, 'some_title' ),

(5, 'some_title' ),

(6, 'some_title' ),

(7, 'some_title' ),

and so on...

news_exclude_table:

id int auto_increment

newsID int

records are like:

('', 2),

('', 3),

('', 7),

('', 10)

and so on...

I removed the auto_increment value. The number is the corresponding newsID

news_like_table:

id int auto_increment,

newsID int

memberID int

records are like:

('', '2', '110'),

('', '4', '110'),

('', '4', '115'),

and so on

I want to select all records from news_table, news_like_table that match a certain condition, but I want to exclude the ones those are also in news_exclude_table

So the selection result of query should have newsID "4" only

I can't figure out the mysql query to use with PHP in this case

select news_table.* from news_table, news_like_table, news_exclude_table where news_table.newsID = news_like_table.newsID and news_like_table.memberID='110' and news_table.newsID not in news_exclude_table.newsID

It should select newsID="4" and leave out newsID="2" because "2" is also in news_exclude_table

I can't get this working.

Kindly help.

Thanks

网友答案:

Switch to explicit joins and either leave the exclusion table out of the join and use a not in () with a subquery, or left join the exclusions table on the other 2, and use not null in the where clause. I'll show you the latter one:

select news_table.*
from news_table
inner join news_like_table on news_table.newsID = news_like_table.newsID
left join news_exclude_table on news_exclude_table.newsID=news_table.newsID
where  news_like_table.memberID='110' and news_exclude_table.newsID is null
相关阅读:
Top