CODEWITHSUNDEEP
sqlpostgresql
user10457989
user10457989

Reputation: 73

Merging results present in all sub queries

I have the a postgre table with a similar representation to the data below.

Caters Table:

-----------------------
| Name    | Option     |
-----------------------
| jane    | social      |
| jane    | vegan       |
| jane    | gmo-free    |
| jane    | italian     |
| jack    | social      |
| jack    | corporate   |
| jack    | gmo-free    |
| jack    | greek       |
| rodz    | social      |
| rodz    | wedding     |
| rodz    | gmo-free    |
| rodz    | vegan       |
| rodz    | french      |

This is the "pseudo" query I'm trying to run

SELECT * FROM caters 
WHERE option is either ['italian', 'french'] 
AND WHERE option is both ['wedding', 'social']

This pseudo query should return rodz. Because it either has italian or french and it has both wedding and social.

This is the query I tried to write to accomplish my sudo query

SELECT c.name FROM caters c
WHERE c.option in ('italian', 'french')
GROUP BY c.name
HAVING array_agg(c.option) @> array['wedding', 'social']

How ever this returns no results. Running the query individually

SELECT c.name FROM caters c
WHERE c.option in ('italian', 'french')
GROUP BY c.name

Result:

-----------
| Name    |
-----------
| jane    | // has italian
| rodz    | // has french

The other query 

SELECT c.name FROM caters c
GROUP BY c.name
HAVING array_agg(c.option) @> array['wedding', 'social']

Result:

-----------
| Name    |
-----------
| rodz    | // has wedding and social

So I can see individually the queries are correct. This made me think well if I have 2 queries giving me the correct results just need to filter out results that are in both queries why don't I JOIN them.

So I tried

SELECT c.name FROM caters c
JOIN caters c1 
ON c1.name = c.name and c1.option = c.option
WHERE c1.option in ('italian', 'french')
GROUP BY c.name
HAVING array_agg(c.option) @> array['wedding', 'social']

But this also yielding no results. Any idea how I can go about this?

NOTE: The query is dynamic each time its ran the values being used could be different sometimes maybes it 5 languages sometimes its 2 languages like in this example ('italian', 'french'). To give an example what I mean by dynamic query another query could be 

SELECT * FROM caters 
WHERE option is either ['italian'] 
AND WHERE option is both ['corporate', 'social']
// returns none
----------------------------------------------------------
SELECT * FROM caters 
WHERE option is either ['french', 'greek'] 
AND WHERE option is either ['gmo-free', 'vegan'] 
AND WHERE option is both ['corporate', 'social']
// returns jack
----------------------------------------------------------
SELECT * FROM caters WHERE option is ['social']
// returns jack, and rodz

Upvotes: 1

Views: 141

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Here is one method:

SELECT c.name
FROM caters c
WHERE c.option in ('italian', 'french', 'wedding', 'social')
GROUP BY c.name
HAVING COUNT(*) FILTER (WHERE c.option IN ('italian', 'french')) >= 1 AND
       COUNT(*) FILTER (WHERE c.option IN ('wedding', 'social')) = 2;

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

You can try using a correlated subquery

DEMO

select distinct name from tablename a 
where option in ('italian', 'french') and exists
(
  select 1 from tablename b where a.name=b.name and option in ('wedding', 'social')
  group by b.name having count(distinct option)=2
)

OUTPUT:

name
rodz

Upvotes: 1

Related Questions