Sumit Kumar Sumit Kumar - 1 year ago 55
MySQL Question

php mysql query selection

I have 3 tables like these

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...


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


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.

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download