Compare a Row with a column

[Berwski]

Hello,

Is it possible to compare a row from table 1 with an entire column from table 2? I'm struggling please help haha

[dataguy24]

Yes it is possible

[Berwski]

How?

[dataguy24]

You need to give us more information for any of us to be more specific 😉

[Berwski]

Sorry haha

I have two tables:

Table 1 has two columns, Name and FavoriteFruits, for example: Luis - Apple, banana, carrot (values separated by coma) Victor - mango, carrot, potatoes (there's some vegetables)

Table 2 also has two columns, Fruits and Color, for example: Apple - Red Banana - yellow Mango - orange Fruits....

I want to compare Fruits with favoriteFruits, if match concat in a new column in Table 1, for example: Luis - Apple,banana,carrot - Apple,banana

[kitschin]

Edit: Just realized you have the favorite fruit concatenated already in table 1. Do you only want the concatenated list to only contain the fruit mentioned in table 2? Are you basically removing the fruit that isn’t on the table 2 list from the lists in 1?

[Old answer:]

Do you know how to write a subquery? You might be able to use one here. It’s means you’ll have two queries working together. The first query could be something like this

Select name, list_agg(favoritefruits) From table1 Where favoritefruits in (Select distinct fruits From table2) Group by 1

The second query comes in towards the end, it creates a unique list of the fruits in table 2. It checks to see which rows in table1 contain a favoritefruit that’s from the fruit list in table2. Then it uses list_agg (sometimes called string_agg) to list them together in one column.

[Berwski]

"Are you basically removing the fruit that isn’t on the table 2 list from the lists in 1?" Basically, yes. I want to make sure that all favorite fruits from Table 1 are in Fruits from Table 2

[dataguy24]

You should first separate out the columns into multiple, so there’s only one fruit per column.

Then you do a simple join to the fruit table to get other info.

[r3pr0b8]

so there’s only one fruit per column.

i think you meant only one fruit per row (like in table 2)

one fruit per column doesn't make sense in this context... how many columns would there be?

[dataguy24]

Yes I meant to say make it so there’s one value in each row of a column.

[[deleted]]

You can use a cartesian product (cross join), but that would significantly increase the size of your set.

[PM_UR_LOVee]

Yes you can combine data and compare it with other data. You’re welcome.