0

can I return a single instance of a row after using a join on a categories table.

Entries

| id | Name   | 
| 1  | Johnny |
| 2  | Steve  |
| 3  | Bam    |

Categories

| cat_id | Name        | 
| 1      | Season one  |
| 2      | Season two  |
| 3      | Season three|

Category Posts

| id | cat_id | 
| 1  | 1      |
| 1  | 2      |
| 1  | 3      |
| 2  | 2      |
| 2  | 3      |
| 3  | 1      |

What I want to do is select all cast where members that have been in season 2 and 3, they must have been in both and I only want a single instance returned.

Expected output

| id | Name   | 
| 1  | Johnny |
| 2  | Steve  |

How would I got about selecting these? I've thought about grouping the user based on their name however because I'm selecting IN ("2", "3") I get some users that have been in two but not three and the expected results are wrong.

Thanks

  • categories look an awful lot like seasons !?! – Strawberry Jul 19 '18 at 10:47
  • 1
    Anyway... `GROUP BY... HAVING COUNT([DISTINCT] ...) = n` where DISTINCT is optional, and n is equal to the number of arguments in `IN()` – Strawberry Jul 19 '18 at 10:49
  • I'm not following? @Strawberry - the content to some degree is irrelevant I was going to use cars and car history, it's the concept I'm after, selecting a single item after multiple joins. –  Jul 19 '18 at 10:51
  • 1
    It's the concept that I've provided! – Strawberry Jul 19 '18 at 10:57
  • The linked duplicate question has multiple different approaches to this question, including the approach @Strawberry has kindly outlined. – Shadow Jul 19 '18 at 11:08

1 Answers1

0

you can use sub query

    SELECT id,name from Entries where id in (select a.id from (select id from 
category_post WHERE cat_id=2) as a,(select id from category_post WHERE cat_id=3) as b where a.id=b.id)
Moneer Kamal
  • 1,837
  • 16
  • 25