CODEWITHSUNDEEP
mysqlsqlpivot-table
meder omuraliev
meder omuraliev

Reputation: 186682

Incorrectly pivoting table after JOINing?

SELECT

  GROUP_CONCAT(if(meta_summary = 'content', content, NULL)) AS content, 
  GROUP_CONCAT(if(meta_summary = 'supplemental', content, NULL)) AS supplemental,
  GROUP_CONCAT(if(meta_summary = 'heading', content, NULL)) AS heading,

cms_pages.meta_filename as filename

FROM
cms_pages

INNER JOIN cms_collection

ON

cms_collection.collection_id LIKE CONCAT('%', cms_pages.page_id,  '/heading%' )
OR cms_collection.collection_id LIKE CONCAT('%', cms_pages.page_id,  '/content%' )
OR cms_collection.collection_id LIKE CONCAT('%', cms_pages.page_id,  '/supplemental%' )

INNER JOIN

cms_content ON cms_collection.collection_id = cms_content.collection_id

WHERE
site_id = 51

GROUP BY
cms_pages.page_id

Schema BEFORE the GROUP BY:

Filename        Content

pageA           (Heading Content)
pageA           (Content)
pageA           (Supplemental Content)

Desired rule-set:

Filename       Heading            Content          Supplemental
pageA          (Heading Content) (Content)         (Supp. Content)

I tried reading http://www.artfulsoftware.com/infotree/queries.php#78 but can't figure out why this isn't working for me - it seems like it's incorrectly group concatenating the rows.. some are right and some look like they incorrectly merged with content from another page. Could this be because I need to do a subquery with the group concat because of all my joins?

Update #1: Created a sqlfiddle @ http://sqlfiddle.com/#!2/fe3e3/1, but it actually works there.. either

a) I didn't recreate it properly b) Maybe it's NaviCat that's messing up the results, though I doubt it

Update #2: Found the reason - it was because the LIKE I was using was (rightfully) matching substrings, so '2/content' would match '22/content' and so forth.

How should I alter my INNER JOIN on cms_collection in an efficient manner to do an exact match?

Upvotes: 2

Views: 67

Answers (1)

user359040
user359040

Reputation:

Try removing the wildcard from the start of the LIKE string-matching join condition, like so:

SELECT
  GROUP_CONCAT(if(meta_summary = 'content', content, NULL)) AS content, 
  GROUP_CONCAT(if(meta_summary = 'supplemental', content, NULL)) AS supplemental,
  GROUP_CONCAT(if(meta_summary = 'heading', content, NULL)) AS heading,
  cms_pages.meta_filename as filename
FROM cms_pages
INNER JOIN cms_collection ON
  cms_collection.collection_id LIKE CONCAT(cms_pages.page_id, '/heading%')
  OR cms_collection.collection_id LIKE CONCAT(cms_pages.page_id, '/content%')
  OR cms_collection.collection_id LIKE CONCAT(cms_pages.page_id, '/supplemental%')
INNER JOIN
  cms_content ON cms_collection.collection_id = cms_content.collection_id
WHERE
  site_id = 53
GROUP BY
  cms_pages.page_id

SQLFiddle here.

Upvotes: 1

Related Questions