Sumit Kumar Sumit Kumar - 1 month ago 5
MySQL Question

php mysql query selection

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

Answer

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
Comments