SQL Query SELECT FROM table "Issue"

Databases
#1

I want to fetch the unique fruits name from column fruit_1 OR fruit_2. I am using OR but I am getting the search value as ‘mango’ also. but instead of this, I am expecting only ‘banana’ and ‘grapes’

Please help how I acheive

ID fruit_1 fruit_2
1 mango banana
2 grapes mango
3 grapes banana
4 grapes lemon

SELECT DISTINCT fruit_1, fruit_2 FROM table WHERE fruit_1 = 'mango' OR fruit_2 = 'mango'

#2

To be honest this makes not much sense even if it’s possible.

Counting column names are always a 100% garanty for a bad table design. So maybe you should think about changing your tables.
If you like, you can ask us to help you but in that case we need more information about what your app is doing and what’s the goal.

So you can use

select case when fruit_1 is ‘mango’ then fruit_2 else fruit 1 end as fruit
from table where fruit_1 = ‘mango’ or fruit_2 = ‘mango’

But this is not very good code and not even very performant

#3

I’m not sure it doesnt make sense, the statement is simple; find all of the items that are paired with another item.

Table design looks like an unnecessary/malformed join table to me, but without the full database picture, shrug

I think it’d be easier to filter it back in the home language doing the query rather than at the database, but Thallius’ code will get you there, as would a union of two queries pulling the opposite fruit. Not the cleanest design, but it’s probably not that far off of what you’d have to do for a cross-lookup anyway.

#4

I’m not quite sure what you’re trying to achieve here.

Your query is returning the rows in which fruit_1 or fruit_2 is mango, hence why you get mango. You’re getting grapes and bananas because sql will return ALL fields that are in the select, not just the ones that match the filter criteria. You get both mango and banana from row 1 because fruit_1 is mango, and you get grapes and mango from row 2 because fruit_2 is mango. You will never see lemon because there’s no row that has lemon in fruit_1 or fruit_2 AND has mango in the other field.

What is the end result you WANT to see? If you give us that, we can try to help you build the query that achieves that.

#5 
SELECT fruit_1 AS fruit_paired_with_mango
  FROM table 
 WHERE fruit_2 = 'mango'
UNION
SELECT fruit_2
  FROM table 
 WHERE fruit_1 = 'mango'
1 Like
#6

I think my solution is faster as it does not need to search the tables two times. But your solution looks nicer

#7

you’re right, your code is awful :sunglasses: (couple typos, plus you forgot the unique aspect)

mine will run very efficiently if both fruit_1 and fruit_2 have separate indexes

edited to delete “but yours cannot” from the end there (i forgot about index merge, not being a DBA)

your CASE is very clear as to intent, but i would be tempted to use

SELECT COALESCE(NULLIF(fruit_1,'mango')
               ,NULLIF(fruit_2,'mango')) AS fruit
  FROM table
 WHERE 'mango' IN (fruit_1,fruit_2)
2 Likes