CODEWITHSUNDEEP
sqlpostgresql
ClaytonJones839
ClaytonJones839

Reputation: 1

Selecting an optional number of values into one column

I am trying to see if it is possible to query more than one value into an alias when a certain condition is met, while inserting one value when a different condition is met. I was looking to see if this is possible without doing a subquery.

I am using postgreSQL 11, I very recently started studying SQL.

For practice, I am using an example from SQLZOO, involving 3 separate tables-

cats (id, name, color, breed)
toys (id, name, price, color)
cattoys (id, cat_id, toy_id)

I have created my own data for these tables and I am looking to see if it is possible to have one single column with anything (cat, or toy) that is a particular color, without doing a subquery.

In the data I am using, I have 5 rows in each table. To test-

'Garfield' is the only 'orange' cat
'hay' and 'yarn' are the only 'orange' toys
'Garfield' has both hay and yarn (the only toys for Garfield)
'Ozzie' has yarn (the only toy for Ozzie)

Would it be possible to run a query resulting in an output similar to:

 orange_stuff 
--------------
 Garfield
 hay
 yarn

I have gotten it close, but I can not get my CASE statement down, to where if cats.color AND toys.color are both 'orange', to insert both items into my alias 'orange_stuff'.

FIRST ATTEMPT:

SELECT
CASE 
WHEN cats.color = 'orange' AND toys.color = 'color' THEN cats.name, toys.name
WHEN cats.color = 'orange' THEN cats.name
WHEN toys.color = 'orange' THEN toys.name
END AS orange_stuff
FROM
    cats 
JOIN
    cattoys ON cattoys.cat_id = cats.id
JOIN
    toys ON toys.id = cattoys.toy_id
WHERE
    cats.color = 'orange' OR toys.color= 'orange';

this will not work, because an error occurs trying to return two arguments in the first WHEN statement

ALTERNATIVE ATTEMPT:

SELECT
CASE 
WHEN cats.color = 'orange' THEN cats.name
WHEN toys.color = 'orange' THEN toys.name
END AS orange_stuff
FROM
    cats 
JOIN
    cattoys ON cattoys.cat_id = cats.id
JOIN
    toys ON toys.id = cattoys.toy_id
WHERE
    cats.color = 'orange' OR toys.color= 'orange';

this will almost do the trick, but the way the CASE statement is set up, when it chooses 'Garfield' over the toys.name both times

 orange_stuff 
--------------
 Garfield
 hay
 Garfield

Would like to see if possible, without a subquery to obtain:

 orange_stuff 
--------------
 Garfield
 hay
 yarn

Upvotes: 0

Views: 57

Answers (1)

Renzo
Renzo

Reputation: 27424

If you need to know only orange things without any relation among them, I think a simple union all (or union) query could produce the result:

SELECT name AS orange_stuff
FROM cats
WHERE color = 'orange'
UNION ALL
SELECT name AS orange_stuff
FROM toys 
WHERE color = 'orange'

Upvotes: 2

Related Questions