CODEWITHSUNDEEP
sqldatabasepostgresql
mingle
mingle

Reputation: 1617

SQL join with 2 conditions

Let's say we have a table "pages" with records

|ID    | Name |
|1     | Test1|
|2     | Test2|

and "categories": 

|PAGE_ID| ID | NAME  |
|1      |  1 | Nice  | 
|1      |  2 | Supper|
|2      |  3 | Nice  |

I need to get all pages that belongs to category "Nice" AND "Supper" in this case we have 1 page => Test1.

Can this be done in 1 SELECT with 1 join, or subselect or second join is needed? The best would be to get a result like 

|PAGE_ID | PAGE.NAME| CATEGORY.NAME_1 | CATEGORY.NAME_2

and then just pick up the right record with a simple where.

Upvotes: 0

Views: 1205

Answers (2)

peterm
peterm

Reputation: 92785

You can do it like this

SELECT p.ID page_id, p.Name page_name, 'Nice' Category1, 'Supper' Category2
  FROM categories c LEFT JOIN
       pages p ON c.PAGE_ID = p.ID
WHERE c.Name IN ('Nice', 'Supper')
GROUP BY p.ID, p.Name
HAVING COUNT(p.ID) = 2

Output:

| PAGE_ID | PAGE_NAME | CATEGORY1 | CATEGORY2 |
-----------------------------------------------
|       1 |     Test1 |      Nice |    Supper |

SQLFiddle

This solution works fine when you need to find pages more than in two categories without continuously chaining JOINs.

Upvotes: 2

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

select
    c1.page_id, p.name, c1.name, c2.name
from
    pages p
    inner join
    categories c1 on p.id = c1.page_id
    inner join
    categories c2 on p.id = c2.page_id
where
    c1.name = 'Nice' and c2.name = 'Supper'

Upvotes: 2

Related Questions