How to avoid cartesian product on querying?

Question

I'm building a web app of classified ads (it's my first professional PHP app). I have a table with the Ads and other with the images for each Ad. Each ad can have up to 4 images. It all worked fine while I was adding only one image per ad. Now, when I ad more than one image per ad that ad repeats in the listing (one time per each image it has). Here's the SQL I'm using:

SELECT 
sun_classified_ads.ad_id, 
sun_classified_ads.insertion_datetime, 
sun_classified_ads.title,                
sun_classified_ads.description, 
sun_classified_ads.maker, 
sun_classified_ads.model, 
sun_classified_ads.location, 
sun_classified_ads.price, 
sun_classified_ads_images.filename_mini, 
sun_classified_ads_images.size_attr_mini, 
sun_classified_ads_images.description AS image_description
FROM sun_classified_ads
INNER JOIN sun_classified_ads_images ON sun_classified_ads.ad_id =       sun_classified_ads_images.ad_id

How can I do so it only uses the first image for that Ad from the sun_classified_ads_images table?

score 2 · Accepted Answer · answered Jan 30 '13 at 02:52

MySQL has a "feature" called Hidden Columns. You can get one image per ad using the following:

SELECT 
sun_classified_ads.ad_id, 
sun_classified_ads.insertion_datetime, 
sun_classified_ads.title,                
sun_classified_ads.description, 
sun_classified_ads.maker, 
sun_classified_ads.model, 
sun_classified_ads.location, 
sun_classified_ads.price, 
sun_classified_ads_images.filename_mini, 
sun_classified_ads_images.size_attr_mini, 
sun_classified_ads_images.description AS image_description
FROM sun_classified_ads
INNER JOIN sun_classified_ads_images ON sun_classified_ads.ad_id =       sun_classified_ads_images.ad_id
group by sun_classified_ads.ad_id

Just a caution. There is no guarantee that this will return the first ad. There is not guarantee that the three fields from images will actually come from the same record (though in practice they do). However, this is probably the easiest way to get what you want.

The following query does do what you want, but is a bit more work:

SELECT 
sun_classified_ads.ad_id, 
sun_classified_ads.insertion_datetime, 
sun_classified_ads.title,                
sun_classified_ads.description, 
sun_classified_ads.maker, 
sun_classified_ads.model, 
sun_classified_ads.location, 
sun_classified_ads.price, 
substring_index(group_concat(sun_classified_ads_images.filename_mini order by ad_image_id), ',', 1), 
substring_index(group_concat(sun_classified_ads_images.size_attr_mini order by ad_image_id), ',', 1), 
substring_index(group_concat(sun_classified_ads_images.description order by ad_image_id), ',', 1) AS image_description
FROM sun_classified_ads
INNER JOIN sun_classified_ads_images ON sun_classified_ads.ad_id =       sun_classified_ads_images.ad_id
group by sun_classified_ads.ad_id

This assumes that ad_image_id is a field that can be used to define the ordering of the images.

I'm now using the second of your solutions, it does it. Thank you very much. — Mick Murillo, Jan 30 '13 at 14:17

score 1 · Answer 2 · answered Jan 30 '13 at 02:51

You did not state if your tables have a datetime as to when the image gets added. If so, then you can alter the below query to return the min() date for each ad.

If not, then you might be able to still use the following:

SELECT 
  a.ad_id, 
  a.insertion_datetime, 
  a.title,                
  a.description, 
  a.maker, 
  a.model, 
  a.location, 
  a.price, 
  i.filename_mini, 
  i.size_attr_mini, 
  i.description AS image_description
FROM sun_classified_ads a
INNER JOIN
(
  select min(ad_id) Min_Ad_id, ad_id
  from sun_classified_ads_images i
  group by ad_id
) i
  ON a.ad_id = i.ad_id
INNER JOIN sun_classified_ads_images i2
  ON a.ad_id = i2.ad_id
  and i.Min_Ad_id = i2.ad_id

If you have a datetime in the images table, then you could use:

INNER JOIN
(
  select min(ad_date) Min_Ad_Date, ad_id
  from sun_classified_ads_images i
  group by ad_id
) i
  ON a.ad_id = i.ad_id
INNER JOIN sun_classified_ads_images i2
  ON a.ad_id = i2.ad_id
  and i.Min_Ad_Date = i2.ad_date

score 0 · Answer 3 · answered Jan 30 '13 at 02:48

The short answer is "you can't". The query you have written will return all rows for all matched data. The data matches once for each image. You need to do two queries -- one to get all the ads, then one inside that on the page to query all the images. That's the most straight forward way to do it.

How to avoid cartesian product on querying?

3 Answers3