Help me with a SQL query? Please?

Factual Questions
1

Either my brain is fried or this is not very easy. I’m stumped.

Let’s say I have this table:

create table fruitygoodness(
id int,
apple char(1),
banana char(1),
cherry char(1),
snozberry char(1)
)

Rows might look like this:


id apple banana cherry snozberry
-- ----- ------ ------ ---------
1  Y     N      Y      Y
2  N     N      N      Y

What I want is to run a query that returns a list of fruit names
where id = (prameter) and fruit = ‘Y’. For instance, where id = 1, I’d return


fruitname
---------
apple 
cherry
snozberry

Here’s the catch: I can’t use a stored procedure and I can’t use temp tables. Any ideas?

2

Is this a legacy table you’re working with? Or do you have control over the table format? Because that looks like a lousy way to format your data. I’d create tables like this:



**Table FruitLookup**:
FruitID   Fruit
--------------
1       Apple
2       Snozberry
3       Cherry
4       Banana
**Table FruitInventory**:

ID   FruitID
------------
1        1
1        3
1        2
2        2

Now if you want to get a list of all the fruit that ID has, just do a Select on ID, with a join on the Fruit table, and you’ll get your list.

This way, your data is normalized better, it’s easier to add new fruit, and the query is simpler.

3

It’s not difficult, but it requires some cleverness, it adds a lot of extra storage, it’ll run slowly, and you’ll have to write a new query every time a new type of fruit is added.

The way you’d do it is with a series of queries UNIONed together. For each type of fruit, you’d have to select all records with the ID equal to the parameter and the field for that fruit equal to Y, display the fruit name, and UNION that with all the other ones. Don’t forget the sorting.

For your example, the code would look something like this pseudocode:



SELECT * FROM
(
    SELECT id, apple, "apple" as Fruit Name WHERE id = parameter and apple = y
    UNION SELECT id, banana, "banana" as Fruit Name WHERE id = parameter and banana = y
    UNION SELECT id, cherry, "cherry" as Fruit Name WHERE id = parameter and cherry = y
    UNION SELECT id, snozzberry, "snozzberry" as Fruit Name WHERE id = parameter and snozzberry = y
)
ORDER BY Fruit Name

Putting a constant in the fruitname field may not be as easy as I’ve imagined it to be here–you might need other nested queries. Unless someone’s got a gun to your head, I suggest you follow Sam Stone’s advice.

4

I, too, will suggest Sam Stone’s method first, but, absent that, ultrafilter has constructed the query that will extract the data as you need. But you’ll hate it.

5

You could create a view and select from the view.

6

The view definition would have to be essentially ultrafilter’s query. I don’t see how that does anything but add an extra layer of complexity. (unless you need to do this query multiple times in multiple places – then you’d only need to update the view when you got new fruit)

7

In regards to the original post, ultrafilter’s solution will work.

But remember that every time the correlation between the position of the Ys and the Ns change, and every time you add another flavor you will have to change your query. Then test it. Then move it into production. Then change your documentation.

Sam Stone’s solution applies the rules of normalization, which I won’t go into here. But essentially it has to do with putting your apples into one basket and your oranges into another, then linking (joining) the two. It allows for much more flexibility. I also urge this solution.

8

Just checking in here, I posted the question but haven’t responded yet because of other code fixes.

I can’t change the format of the data, but I could add another table if need be. I can’t use a view. I’m going to try the UNION thing but I remember something about passthrough queries not liking them.

9

Fruits Table:

FruitName

Apple
Snozberry
Cherry
Banana

select
FruitName

from
Fruitygoodness,Fruits

where (FruitName=‘Apple’ and apple=‘Y’)
or (FruitName=‘Snoz’ and snoz=‘Y’)
or (FruitName=‘Cherry’ and cherry=‘Y’)
or (FruitName=‘Ban’ and ban=‘Y’)

10

Ah, Clever. But by the time you posted, I was knee deep in the UNION solution, which as it turns out will work. Below is my final, bloody solution. Thanks for the input!

SELECT GroupName, * FROM GenSurgFollowupData fd
INNER JOIN GenSurgFollowupLookup fl
on fd.ElementName = fl.ElementName
WHERE GroupName in(SELECT CASE Diabetes
when ‘Y’ then ‘Diabetes’
else ‘xxxxxxx’
end ComorbCond from tpn.smartsleep_bariatric
WHERE WMRN = ‘12345678’
UNION
SELECT CASE Hypertension
when ‘Y’ then ‘Hypertension’
else ‘xxxxxxx’
end from tpn.smartsleep_bariatric
WHERE WMRN = ‘12345678’
UNION
SELECT CASE HyperChol
when ‘Y’ then ‘HyperChol’
else ‘xxxxxxx’
end from tpn.smartsleep_bariatric
WHERE WMRN = ‘12345678’
UNION
SELECT CASE LowBack
when ‘Y’ then ‘LowBack’
else ‘xxxxxxx’
end from tpn.smartsleep_bariatric
WHERE WMRN = ‘12345678’
UNION
SELECT CASE Arthritis
when ‘Y’ then ‘Arthritis’
else ‘xxxxxxx’
end from tpn.smartsleep_bariatric
WHERE WMRN = ‘12345678’
UNION
SELECT CASE Asthma
when ‘Y’ then ‘Asthma’
else ‘xxxxxxx’
end from tpn.smartsleep_bariatric
WHERE WMRN = ‘12345678’
UNION
SELECT CASE Reflux
when ‘Y’ then ‘Reflux’
else ‘xxxxxxx’
end from tpn.smartsleep_bariatric
WHERE WMRN = ‘12345678’
UNION
SELECT CASE Apnea
when ‘Y’ then ‘Apnea’
else ‘xxxxxxx’
end from tpn.smartsleep_bariatric
WHERE WMRN = ‘12345678’
UNION
SELECT CASE Incont
when ‘Y’ then ‘Incont’
else ‘xxxxxxx’
end from tpn.smartsleep_bariatric
WHERE WMRN = ‘12345678’
UNION
SELECT CASE Infertility
when ‘Y’ then ‘Infertility’
else ‘xxxxxxx’
end from tpn.smartsleep_bariatric
WHERE WMRN = ‘12345678’)
AND fd.WMRN = ‘12345678’