mysql get more than one result from join query

Question

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')

(tagRel.catalog_id=catalog.catalog_id)? Okay, you changed your post, but did it fix anything when you tested it? — user645280
– user645280, Commented Dec 19, 2012 at 20:58
I guess I am not understanding the question. In your example query, it seems that you are only looking for a single tag with tag_slug = 'dinosaurs'. The way your schema is set up, you should probably only have one tag with that tag_slug in the tag table, thought you might have multiple records in catalog associated with it. Why do you expect to return more than one tag when you are only querying for one tag? — Mike Brant
– Mike Brant, Commented Dec 19, 2012 at 20:59
Sorry ebyrob, I fixed the query (was simplifying it down) for the post. @MikeBrant: A catalog item can have multiple tags (a dinosaur book can be have tags for: book, childrens, dinosaur,etc.). I want to do a search on the tag 'dinosaur' to retrieve any item and its relevant tags (including the one that I am searching on). — daxiang28
– daxiang28, Commented Dec 19, 2012 at 21:03
So, you want to get all the tags associated in each row of the result? (or set of rows) — user645280
– user645280, Commented Dec 19, 2012 at 21:05
@ebyrob Yes. I pretty much want to get all the tags if similar to if I swapped out the where statement to: WHERE (catalog.name='sbc') — daxiang28
– daxiang28, Commented Dec 19, 2012 at 21:08

score 1 · Accepted Answer · 2012-12-19 21:55:38Z

1

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.

edited Dec 19, 2012 at 21:55

answered Dec 19, 2012 at 21:11

user645280

Sign up to request clarification or add additional context in comments.

6 Comments

daxiang28 Over a year ago

Wouldn't this yield multiple rows of the same catalog item? I guess another way to phrase the question is that I am looking to filter a result set of catalog items based on a tag name.

user645280 Over a year ago

I don't think so? I can make a set of test tables if it's not working as expected... It may need a GROUP BY, but initially I was thinking it wouldn't (assuming tags are only related to catalogs once).

daxiang28 Over a year ago

Thanks @ebyrob. I was wondering if there was a more modular approach to augmenting my first original query (subqueries or what not). I am trying to avoid a full rewrite of the larger more complex query if at all possible.

user645280 Over a year ago

Well, if you want flexibility to change the where clause and do things like multiple tagnames combined with other criteria, then a subquery returning just a list of IDs is the way to go.

daxiang28 Over a year ago

Thanks @ebyrob. I ended up going with a subqury. Was just a bit rusty on the syntax.

|

Collectives™ on Stack Overflow

mysql get more than one result from join query

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related