# Thread: How many of a series is in another series

1. ## How many of a series is in another series

SO here is how my logic works, if you start with this basic query

SELECT group_concat(DISTINCT some_fr_key) FROM tbl1
group by some_fr_key

You get a list of some_fr_key, comma separated.

Now you can do
SELECT count(*) from tbl1
where some_fr_key IN (1,2,3,4,5)

Gives you the number of times some_fr_key has been used and was in the series of numbers, essentially how many of one series is in another series.

What I'm trying to do was
select count( some_fr_key in (1,2,3,4,5) from tbl1

Now much to my logical flaw this returns 1 possibly 2 haven't figured out why. Because in is boolean and returns correct if some_fr_key was in taht series.

How can I do "SELECT count(*) from tbl1
where some_fr_key IN (1,2,3,4,5)" in the select w/o a sub select?

2. Above question is a little too confusing, here is a simpler version

You have two tables

table 1. people has key id
table 2. people_colors has people_id, and color_id

I want to do
Code MySQL:
```SELECT count(pc.color_id), group_concat(pc.color_id),
FROM people p
LEFT JOIN people_colors pc ON (pc.people_id = p.id)
GROUP BY people.id```

This returns me the number of colors, and a list of the colors per person.

I also want to know how many colors a person has in a a given list of color_id?

3. Are you looking for something like this:

Code SQL:
```CREATE TABLE people (
people_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT
,name VARCHAR(24)
,PRIMARY KEY(people_id)
);

CREATE TABLE colors (
color_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT
,name VARCHAR(24)
,PRIMARY KEY(color_id)
);

CREATE TABLE people_colors (
people_colors_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT
,people_id MEDIUMINT UNSIGNED NOT NULL
,color_id MEDIUMINT UNSIGNED NOT NULL
,PRIMARY KEY(people_colors_id)
,INDEX(people_id)
,INDEX(color_id)
);```

Code SQL:
```SELECT
p.people_id
,GROUP_CONCAT(CONCAT(c.name,':',COALESCE(pc.colors,0))) AS colors
FROM
people p
CROSS
JOIN
colors c
LEFT
JOIN
(SELECT
pc.people_id
,pc.color_id
,COUNT(*) AS colors
FROM
people_colors pc
GROUP
BY
pc.people_id
,pc.color_id) AS pc
ON
p.people_id = pc.people_id
AND
c.color_id = pc.color_id
GROUP
BY
p.people_id```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•