JOIN vs regular select - problem

not receiving the same records

[asantos]

Hi, im trying to get all the ADS from a table, that have not been already deleted by the user (deleted flag is on a separated table).

1. This is the JOIN method (where i have the problem):
SELECT ads.id_ad, ads.date, ads.title, adclick.id_click FROM ads LEFT OUTER JOIN adclick ON ads.id_ad = adclick.id_ad WHERE (adclick.delete_ad=0) AND (adclick.id_user=25) ORDER BY date DESC

It should bring all the ads (read and unread), except those that have been deleted by the user... but it only shows the ones that have been read and not deleted. The ones that are not linked between both tables are not appearing.

2. This is the regular select(it works with this one!)
SELECT ads.id_ad,ads.title,ads.date,ads.msg,adclick.id_click FROM ads, adclick WHERE adclick.delete_ad=0 AND adclick.id_user=25 ORDER BY date DESC

This one works alright! It brings both the unread and read+not deleted ads.

Of course i would prefer to use the JOIN sentence (1) because it optimizes the query. Still... it is not bringing me the whole records.

Any ideas?

Im attaching both table structures below:

CREATE TABLE `ads` (
`id_ad` int(11) NOT NULL auto_increment,
`date` int(14) NOT NULL default '0',
`title` varchar(100) NOT NULL,
`msg` varchar(255) default NULL,
PRIMARY KEY (`id_ad`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE `adclick` (
`id_click` int(11) NOT NULL auto_increment,
`id_ad` int(11) default NULL,
`id_user` int(11) NOT NULL,
`delete_ad` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`id_click`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

[Demaestro]

Try

SELECT ads.id_ad, ads.date, ads.title, adclick.id_click FROM ads LEFT JOIN adclick ON ads.id_ad = adclick.id_ad WHERE (adclick.delete_ad=0) AND (adclick.id_user=25) ORDER BY date DESC

All I did was change LEFT OUTER JOIN --> LEFT JOIN

[asantos]

Didnt work... =(
The way i see it... it has to be OUTER because i need ALL the records from "ads" despite if you have the same records in adclick or not.

[coopster]

LEFT [OUTER] JOIN

There is no implicit join-notation for outer joins in standard SQL, both are the same.

I think what you are likely missing here is some form of

IS NULL

logic in your WHERE clause. It is the most common issue when folks are doing LEFT JOIN syntax and getting unexpected or incomplete result sets.

[asantos]

coopster, you saved my day!

SELECT ads.id_ad, ads.date, ads.title, adclick.id_click FROM ads LEFT JOIN adclick ON ads.id_ad = adclick.id_ad WHERE (((adclick.delete_ad=0) AND (adclick.id_user=25)) OR (adclick.id_click IS NULL)) ORDER BY date DESC

That works just right! Thanks!

[asantos]

Damn... just realized that i have an error on my last statement.

To simplify things in this forum, i've shorten the query to this:
SELECT COUNT(*) AS q FROM ads LEFT JOIN adclick ON ads.id_msg = adclick.id_msg WHERE
(adclick.id_user != 25) AND (adclick.id_click IS NULL)

It will sure bring me all the records from ADS that user 25 has not previously clicked them... but it will also bring me all the clicked records of all other users... :s