CODEWITHSUNDEEP
mysql
daxiang28
daxiang28

Reputation: 553

mysql get more than one result from join query

I'm having trouble returning more than one 'tag' from the 'catalog_tag' table when I perform a search query for a specific tag. If I do a search on 'catalog.catalog_id', I do get all the associated tags via the inner joins. Is there a way to grab the tags when searching for a specific tag?

Tables:

catalog table has: catalog_id|name|post_date

catalog_tag_relationship has: catalog_id|tag_id

catalog_tag has: tag_id|tag_name

SQL:

SELECT catalog_id, name, tag.tag_id, tag.tag_name 
FROM catalog   
INNER JOIN catalog_tag_relationship tagRel ON (tagRel.catalog_id=catalog.catalog_id)  
INNER JOIN catalog_tag tag ON (tagRel.catalog_tag_id=tag.tag_id)  
WHERE (tag.tag_name='dinosaurs')

Upvotes: 2

Views: 212

Answers (1)

user645280
user645280

Reputation:

Revised: 

SELECT
  catalog.catalog_id,
  catalog.name,
  tag.tag_id,
  tag.tag_name
FROM (
  SELECT
    catalog.catalog_id as search_id
  FROM catalog
  INNER JOIN catalog_tag_relationship tagRel
          ON tagRel.catalog_id=catalog.catalog_id
  INNER JOIN catalog_tag tag ON tagRel.catalog_tag_id=tag.tag_id
  WHERE tag_name='dinosaurs'
  GROUP BY catalog.catalog_id
) AS searchList
INNER JOIN catalog ON catalog.catalog_id = search_id
INNER JOIN catalog_tag_relationship tagRel
        ON tagRel.catalog_id=catalog.catalog_id
INNER JOIN catalog_tag tag ON tagRel.catalog_tag_id=tag.tag_id

EDIT: This should return the same results as choosing a single list from the catalog table.

Step 1: Find list of catalog ID's matching search criteria.

Step 2: Fill in all catalog information for catalog ID's found in step 1.

This will return multiple rows per catalog entry, but only 1 row per unique catalog <-> tag mapping. If you want one row per catalog you'd need GROUP_CONCAT() to see all the different tags for that catalog.

Upvotes: 1

Related Questions