CODEWITHSUNDEEP
mysqlsql
DaveS
DaveS

Reputation: 3

Retrieve records from multiple tables some distinct, some not

I have 4 tables in an existing mysql database of a directory type site. Table mt_links contains basic info for each listing Table mt_cl contains which listing above is in what category (I only want cat_id=1) Table mt_cfvalues contains more details for each listing It Can have repeated values Table mt_images contains image names for each listing.

I want all records from mt_links where the mt_cl cat_id=1, and for each of those records, I need all records in mt_cfvalues and cf_images matching the link_id.

I set up a select with Group_Concat and left joins, but ended up with repeating values in my results. I added Distinct, which cured the repeating values, but mt_cfvalues can have records with the same value, so now I'm missing a value I should have.

SELECT    a.link_id, 
          a.link_name,
          a.link_desc,
          GROUP_CONCAT(DISTINCT b.value ORDER BY b.cf_ID) AS details,
          GROUP_CONCAT(DISTINCT c.filename ORDER BY c.ordering) AS images
FROM      mt_links a
LEFT JOIN mt_cfvalues b ON a.link_id = b.link_ID
LEFT JOIN mt_images c ON b.link_id = c.link_ID
LEFT JOIN mt_cl d ON a.link_id = d.link_ID WHERE d.cat_ID = '1'
GROUP BY  a.link_id

I put together a SQLFiddle here: http://www.sqlfiddle.com/#!2/f39e9/1

Is there an easier way? How do I fix the repeating / no repeating issue?

Upvotes: 0

Views: 53

Answers (1)

James
James

Reputation: 22247

Here is one way of accomplishing what you seek. Because the two subqueries return independent results, you can't combine the GROUP BY, which is why you were getting duplicates.

SELECT    a.link_id, 
          a.link_name,
          a.link_desc,
          cvf.details,
          imgs.images
FROM      mt_links a
LEFT JOIN (
  SELECT link_ID, GROUP_CONCAT(value ORDER BY cf_ID) AS details
     FROM mt_cfvalues
     GROUP BY link_ID
  ) cvf ON cvf.link_ID = a.link_id
LEFT JOIN (
  SELECT link_ID, GROUP_CONCAT(filename ORDER BY ordering) AS images
     FROM mt_images
     GROUP BY link_ID
  ) imgs ON imgs.link_ID = a.link_id
INNER JOIN mt_cl d ON a.link_id = d.link_ID 
WHERE d.cat_ID = '1'

Upvotes: 0

Related Questions