[SOLVED] MySQL JOIN help

[dmarchman]

I have 2 tables, one stores products and the other stores multiple images for each product here is my SQL to write both:

 

CREATE TABLE `prod` (
  `id_prod` int(11) NOT NULL auto_increment,
  `cat_prod` int(11) NOT NULL default '0',
  `title_prod` text,
  `desc_prod` longtext,
  `reason_prod` text,
  `qty_prod` int(11) default NULL,
  `price_prod` decimal(6,2) NOT NULL default '0.00',
  `location_prod` varchar(255) default NULL,
  `manu_prod` text,
  `typesale_prod` text,
  `update_prod` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id_prod`)
);

CREATE TABLE `gal` (
  `id_gal` int(11) NOT NULL auto_increment,
  `idprod_gal` int(11) NOT NULL default '0',
  `img_gal` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id_gal`)
);

 

The tables are linked with id_prod & idprod_gal. The gal table can store the names of multiple images for each product. I want to query the prod table to give me a list of all the products in it, but join the gal table to pull out one image to display. My current attempts of writing this see the images as the unique items and therefore display a product several times, once for each instance of an image. This is the query that does this:

 

SELECT *
FROM prod INNER JOIN prodcats ON cat_prod = id_prodcats INNER JOIN gal ON id_prod = idprod_gal
ORDER BY update_prod DESC

 

Is there something simple I am missing here. I appreciate any help I can get.

[Adam]

Really not as complex as you may think:

 

select * from prod p, gal g
where p.id_prod = g.idprod_gal
order by p.update_prod

[kickstart]

Hi

 

Problem appears to be that with multiple occurrences of images for a product you will get duplicates returned.

 

Personally I would do a join against a subselect of the gallery which brings back single images per product, and then join that with the gallery table again to get the full details.

 

The subselect might be:-

 

SELECT idprod_gal, MAX(id_gal) FROM gal GROUP BY idprod_gal

 

Putting it together something like this:-

 

SELECT *
FROM prod 
INNER JOIN prodcats ON prod.cat_prod = prodcats.id_prodcats 
INNER JOIN (SELECT idprod_gal, MAX(id_gal) FROM gal GROUP BY idprod_gal) Deriv1 ON prod.id_prod = Deriv1.idprod_gal
INNER JOIN gal ON gal.idprod_gal = Deriv1.idprod_gal
ORDER BY update_prod DESC

 

This is grabbing the image which has the max gal id for that product, but you could use min if you wanted.

 

All the best

 

Keith

[dreamwest]

Problem appears to be that with multiple occurrences of images for a product you will get duplicates returned.

 

You can use distinct within a join but requires a group by the distinct value to work

 

select distinct * from prod as p, gal as g 
where p.id_prod = g.idprod_gal group by idprod_gal order by p.update_prod

[dmarchman]

Really not as complex as you may think:

 

select * from prod p, gal g
where p.id_prod = g.idprod_gal
order by p.update_prod

 

That produces the same thing I'm getting with my original query. It duplicates the products for every image in the gal table assigned to it. Thanks for the suggestion, though.

[dmarchman]

Problem appears to be that with multiple occurrences of images for a product you will get duplicates returned.

 

You can use distinct within a join but requires a group by the distinct value to work

 

select distinct * from prod as p, gal as g 
where p.id_prod = g.idprod_gal group by idprod_gal order by p.update_prod

 

That worked! Sweet beans! Thanks so much! I'm kicking myself for how simple that is.