I have a couple of tables where I am trying to conditionally show an advertisement in the webpage based on a brand value... cannot quite figure out the query to make this happen: I have a table called "ads" and a table called "brands" and an intermediary table called ads_brands. Ads looks like: id, url, title Brands looks like: id, type, brand_name Ads_Brands looks like: id, fk_brand_id, fk_ad_id, status (which can be include or exclude) The thought process is that a coke ad should never show up when a pepsi brand is presented in the query, but any ads marked as being for 'All' or specifically for the brand in questions should be in the found set. Ads: 1 ¦ coke_ad_one.jpg ¦ This is an ad 2 ¦ pepsi_ad_one.jpg ¦ This is another ad 3 ¦ house_ad_one.jpg ¦ This is yet another ad Brands: 1 ¦ Drink ¦ Coke ¦ Coca Cola 2 ¦ Drink ¦ Pepsi ¦ Pepsi Cola 3 ¦ Admin ¦ All ¦ This is a dummy brand used to link an ad to ALL brands Ads_Brands: ID¦ FK_BRAND_ID ¦ FK_AD_ID ¦ STATUS 1 ¦ 3 (all) ¦ 1(coke ad) ¦ Include <--- shows the coke ad on all requests 2 ¦ 2(pepsi) ¦ 1(coke ad) ¦ Exclude <--- trumps the coke ad when pepsi brand presented 3 ¦ 3(all) ¦ 2(pepsi ad)¦ Include <--- shows the pepsi ad on all requests 4 ¦ 1(coke) ¦ 2(pepsi ad)¦ Exclude <--- trumps the pepsi ad when coke brand presented So... I need to fetch a single random ads.url where the fk_brand_id = 3 (good for all) and is not exluded from the current brand id (if there is an exclusion for that brand... not an issue for all ads) I am passing the current brand id in the url to the script... If I pass brand 1 to the script, then I would expect to get ads.id 1 or 3 back (not 2) if I pass brand 2 then I would expect to get 2 or 3 back If I pass brand 52 then I would expect to get 1,2 and 3 back as possible ads (although I will rand() and limit 1) I would never pass brand 3 since it is only a placeholder to all me to make a single ad available to all my brands and not have to keep a monster list in includes... Here is what I am doing wrong... SELECT ads.url FROM ads join ads_brands on ads_brands.fk_brand_id in (select fk_brand_id from ads_brands where fk_brand_id in (1,3) and status != 'Exclude') This is meant to pass the brand 'coke' and I want to get the urls of the ads that are marked as all AND not excluded specifically from the 'coke' brand (by id) I would expect to see ad id 1 and 3 from my model above... but alas... I get all the ads... Cannot seem to wrap my mind around this one... Any takers? I am sure it will all be simple enough to one of you folks...
Try this, I think you're close, just the syntax looks a little incorrect. SELECT Ads.url FROM Ads LEFT JOIN Ads_Brands ON Ads.id = Ads_Brands.FK_AD_ID WHERE (Ads_Brands.FK_BRAND_ID IN (1,2) AND Ads_Brands.STATUS <> 'Exclude') And if you're going to pass a brand ID to the script, you would use: SELECT Ads.url FROM Ads LEFT JOIN Ads_Brands ON Ads.id = Ads_Brands.FK_AD_ID WHERE (Ads_Brands.FK_BRAND_ID = 'MY_BRAND_ID' AND Ads_Brands.STATUS <> 'Exclude') You don't need to exclude the #3 brand if you pass a specific brand to the query, so just replact the IN with = MY_VALUE
When I do this, I get NO results... hmmmm. I would expect to see the ad 1 and 3 in the results and not 2... but it is blank results... missing something... Somehow, I need to say show me all ads where they are linked to the 3 (all) brand.... which is the default action for ads Then refine the find with a condition that says, if the brand id passed (eg 1) has an 'exclude' entry present (pepsi[2] ads should not show when coke brand id [1] is presented) then show only the coke ad and any ads that have no excludes and are bound to either coke[1] brand specifically or to the generic [3] group... A constraint on the initial found set? Or do I simply not find the excluded entries to start with? This will get more complex as more ads go online and are bound to specific brands or excluded from other brands... so getting this working at the simplest level is critical but seemingly difficult.