JOIN issue

[stef25]

ok, rewrote original post. this returns all records from news_placements that ALSO have an associated image. now im trying to make it return records from news_placements that do NOT have an associated image. I know this is currently not happening because of my second WHERE clause, but i need this to select the right image associated with the news items that have 2 images.

my query would work if i could do the following:

if: news_placements.id does not occur in images.news_id_fk,
then use "where news_placements.placement = 0"

if the first condition is not met (ie there is no image associated with a record in the news_placements table), then dont include this WHERE statement.

is that possible in mysql 5?

$sql = "
SELECT
`news_placements`.`id`,
`news_placements`.`title`,
`news_placements`.`short_text`,
`news_placements`.`artist_id_fk`,
`news_placements`.`placement`,
`news_placements`.`date`,
`images`.`path_small`
FROM
news_placements
left Join `images` ON `news_placements`.`id` = `images`.`news_id_fk`
WHERE
`news_placements`.`placement` = 0
AND
images.news_thumb = 1
ORDER BY
`news_placements`.`date` DESC
LIMIT 30
";

[syber]

Since you are using a LEFT JOIN on images, this should work:

$sql = "
SELECT
`news_placements`.`id`,
`news_placements`.`title`,
`news_placements`.`short_text`,
`news_placements`.`artist_id_fk`,
`news_placements`.`placement`,
`news_placements`.`date`,
`images`.`path_small`
FROM news_placements left Join `images`
ON `news_placements`.`id` = `images`.`news_id_fk`
WHERE
('images'.`news_id_fk` IS NOT NULL
AND
images.news_thumb = 1 )
OR `news_placements`.`placement` = 0
ORDER BY
`news_placements`.`date` DESC
LIMIT 30
";

[stef25]

sorry that wont work ... news_id_fk is NULL often since this the images table for my entire site and it contains many items that arent associated with any news items (galleries, press releases, etc)

[syber]

Did you try it?