Collapsing multiple rows into one. 1

[deadpool42]

I'm setting up a database of links, where each one can be in multiple categories. I could make each category id a column in the links table, but I'd rather store it in a separate table so I don't have a bunch of NULLs cluttering up the main table and also to allow unlimited categories. That table has two columns, linkid and categoryid. The problem is that I'd like to retrieve it as one row somehow, something like this: "linkid category1 category2 etc..."

 

[r937]

use the GROUP_CONCAT function

r937.com | rudy.ca

 

[deadpool42]

Thanks! That's a very helpful function although I had to use CAST() to get it to return something useful.

 

[deadpool42]

Now I have a followup question, how would I look up a link by the categories it's in? I want to do this:

SELECT * FROM links
LEFT JOIN catlist ON catlist.linkid = links.id
WHERE GROUP_CONCAT(catlist.categoryid) = "1,2,3"

but MySQL doesn't approve of that.

 

[r937]

how would you look up a link by the categories it's in?

could you rephrase this please?



r937.com | rudy.ca

 

[deadpool42]

I meant that I'd like to get all the links that are in categories 1, 2, and 3 with the categories stored in multiple rows as I mentioned earlier. They need to be in all of those categories, so IN doesn't help.

 

[r937]

if you are creating the comma-delimited list of ids using the GROUP_CONCAT, then your requirement (finding all links in categories 1,2,3) is much easier to solve from the base tables, rather than from the result of the GROUP_CONCAT

not to mention the problem of searching for links that belong to categories 1,3,5 when the lists are ...

1,2,3,5
1,3,4,5
1,2,3,4
1,2,3,4,5
etc.

r937.com | rudy.ca

 

[deadpool42]

Actually, that wouldn't be problem because I'd want the ones that are ONLY in 1, 3, and 5 (and in that order, too). I can't quite wrap my head around the problem (or maybe this design won't work), but here's a better illustration:

In the table links, I have this data:

id
_____
1
2

In the table catlist, I have this data:

id	linkid	categoryid
_____
1	1	1
2	1	2
3	2	1

I want this query to return a linkid of 1:

SELECT (magic query goes here)
WHERE categoryid = "1,2"

 

[r937]

try like so --

SELECT links.* 
  FROM links
INNER 
  JOIN (
       select linkid
            , GROUP_CONCAT(categoryid) as catstring
         from catlist
       group
           by linkid
       ) as c
    ON c.linkid = links.id
   AND c.catstring = '1,2,3'

r937.com | rudy.ca

 

[deadpool42]

Awesome, that works perfectly!