[SOLVED] Help with QUERY

[Canman2005]

Hi all

 

I really need a hand, im getting into slightly complext QUERIES and really could do with some help.

 

Basically I have the following QUERY

 

SELECT p.id, p.image_1, p.title, p.description, p.price, p.live , p.category, l.product_id, l.allergy_id FROM products p LEFT JOIN allergies_products l ON (p.id = l.product_id) WHERE p.live = 1 AND p.category = '15' AND l.allergy_id !=17 GROUP BY l.product_id ORDER BY p.title ASC

 

This gets all products from a main `products` table, but ignores any rows which are linked in the `allergies_products` table which have a `allergy_id` of 17 (or whatever the variable is)

 

inside the table `allergies_products` I store;

 

id

product_id

dietary_id

 

although my QUERY is looking anything without `l.allergy_id !=17` it could be that the same product ID is listed twice in the `allergies_products` table but with different `allergy_id` values.

 

How can I get the QUERY to ignore any rows with a `allergy_id` value of 17 (like it currently does), but then also ignore all rows that have the same `product_id` value as the rows don't have an `allergy_id` value of 17.

 

Does that make any sense?

 

Please can some help me? You're my only hope

 

Thanks in advance :'(

[Canman2005]

Any ideas anyone? Still stuck on this

[Canman2005]

Can anyone suggest anything? Better way to do the QUERY or DB?

[Barand]

Got any data we can test with?

 

for now

SELECT p.id, p.image_1, p.title, p.description, p.price, 
    p.live , p.category, l.product_id, l.allergy_id 
FROM products p 
    LEFT JOIN allergies_products l ON (p.id = l.product_id) AND l.allergy_id != 17
WHERE p.live = 1 AND p.category = '15'          
GROUP BY l.product_id 
ORDER BY p.title ASC

[Canman2005]

Thanks, but that seemed to return incorrect too.

 

Here is some sample data

 

-----------------The Allergies Products Table ----------------------------

 

CREATE TABLE IF NOT EXISTS `allergies_products` (

  `id` int(4) NOT NULL auto_increment,

  `product_id` int(10) NOT NULL,

  `allergy_id` int(10) NOT NULL,

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

 

INSERT INTO `allergies_products` (`id`, `product_id`, `allergy_id`) VALUES

(5, 1, 13),

(4, 1, 28),

(6, 1, 31),

(7, 2, 30),

(8, 3, 31);

 

-----------------The Products Table ----------------------------

 

CREATE TABLE IF NOT EXISTS `products` (

  `id` int(4) NOT NULL auto_increment,

  `category` int(4) NOT NULL default '0',

  `title` varchar(255) NOT NULL default '',

  `image_1` int(4) default NULL,

  `description` text,

  `live` int(1) NOT NULL default '0',

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

 

--

-- Dumping data for table `products`

--

 

INSERT INTO `products` (`id`, `image_1`, `title`, `description`, `price`, `live`, `category`) VALUES

(1, 15, 'Burger', '', 2.99, 1, 1),

(2, 12, 'HotDog', '', 2.99, 1, 1),

(3, 15, 'Sausage Rolls', '', 2.99, 1, 1);

 

 

 

So as you can see, product ID 1 is listed 3 times in the `allergies_products` table, so with my QUERY, if you selected NOT to rerturn any rows with the value 31 in the `allergy_id` field, then the results would only be

 

(7, 2, 30)

 

because `allergy_id` 31 is lined to `product_id` 1 and 3, therefore it shouldnt return any rows which have ID 1 or 3 in them.

 

Does that make any kind of sense?

 

Thanks very much

[Canman2005]

Can anyone help? Still stuck trying to crack this

[Barand]

any closer?

SELECT p.id, p.image_1, p.title, p.description, p.price,
    p.live , p.category, l.product_id, l.allergy_id
FROM products p
  LEFT JOIN allergies_products l ON l.product_id = p.id
WHERE p.live = 1
AND p.category = '1'
AND p.id NOT IN (
  SELECT product_id FROM allergies_products
  WHERE allergy_id = '31'
)
ORDER BY p.title ASC

[Canman2005]

nope, not sure why, but doesnt seem to be returning all the rows from the `products` table

 

any ideas why?

[Barand]

it won't show products where the product has an allergyID of 31

[Canman2005]

managed to tweak it slightly, thanks tons man, saved my frickin life

 

peace out