Sql - Oracle

[Peckers]

I'm trying to construct this query: What's the least popular product(s)?


SELECT FURNITURENAME, FURNITURENO, 'NO ORDERS' AS STATUS
FROM MY_FURNITURE
WHERE FURNITURENO NOT IN
(SELECT FURNITURENO FROM MY_FURNITUREORDER)
UNION
SELECT FURNITURENAME, FURNITURENO, 'LEAST ORDERED' AS STATUS
FROM MY_FURNITURE WHERE FURNITURENO IN
(SELECT FURNITURENO
FROM MY_FURNITUREORDER
GROUP BY FURNITURENO HAVING COUNT(FURNITURENO) =
(SELECT MIN(COUNT(FURNITURENO))
FROM MY_FURNITUREORDER
GROUP BY FURNITURENO))
ORDER BY FURNITURENO;

First Select will return the furnitureno of furniture(s) that have never been ordered.
Second Select will return the furnitureno of the furniture(s) with the least number of orders.

This all works fine, but I only want the second Select to run if there is no furnitures with no(NULL) orders.
Any ideas?

[sky]

Originally posted by Peckers
I'm trying to construct this query: What's the least popular product(s)?


SELECT FURNITURENAME, FURNITURENO, 'NO ORDERS' AS STATUS
FROM MY_FURNITURE
WHERE FURNITURENO NOT IN
(SELECT FURNITURENO FROM MY_FURNITUREORDER)
UNION
SELECT FURNITURENAME, FURNITURENO, 'LEAST ORDERED' AS STATUS
FROM MY_FURNITURE WHERE FURNITURENO IN
(SELECT FURNITURENO
FROM MY_FURNITUREORDER
GROUP BY FURNITURENO HAVING COUNT(FURNITURENO) =
(SELECT MIN(COUNT(FURNITURENO))
FROM MY_FURNITUREORDER
GROUP BY FURNITURENO))
ORDER BY FURNITURENO;

First Select will return the furnitureno of furniture(s) that have never been ordered.
Second Select will return the furnitureno of the furniture(s) with the least number of orders.

This all works fine, but I only want the second Select to run if there is no furnitures with no(NULL) orders.
Any ideas?

i have to admit it's been almost a year since i've last worked an oracle-db, but i'll try and help out anyway :)

eh.. wouldn't the very first select return a table with FURNITURENAME, FURNITURENO and a 3rd column filled with the constant 'NO ORDERS' including only items with a FURNITURENO that isn't listed in MY_FURNITUREORDER (=haven't been ordered)?

the following select would generate a table (that is joined with the one described above) with FURNITURENAME, FURNITURENO and the 3rd column filled with the constant 'LEAST ORDERED' including items whose FURNITURENO is listed in MY_FURNITUREORDER (=have been ordered) grouped by items that have the same amount of orders (?)

analyzing the code as i'm typing this stuff.. just following my flow, til i come up with something, showing it just so you might point me where i did get something wrong (if).


ok got an idea...


SELECT FURNITURENAME, FURNITURENO, 'LEAST ORDERED' AS STATUS
FROM MY_FURNITURE WHERE (FURNITURENO IN
(SELECT FURNITURENO
FROM MY_FURNITUREORDER
GROUP BY FURNITURENO HAVING COUNT(FURNITURENO) =
(SELECT MIN(COUNT(FURNITURENO))
FROM MY_FURNITUREORDER
GROUP BY FURNITURENO))) AND
(COUNT(SELECT FURNITURENO FROM MY_FURNITURE
WHERE FURNITURENO NOT IN
(SELECT FURNITURENO FROM MY_FURNITUREORDER)) IS NULL)



eh.. whether that'll work or not - i dunno, but i hope it points you into the direction i'm thinking. gotta say again that my sql is a little rusty - not having oracle or mysql around to try it out.
basically my idea for the 2nd select is to have two criteria to select. the one is all yours and the other is supposed to see if there is items with no orders and if so it should start working on what you had done before... if there aren't items with no orders, one of the two criteria isn't fulfilled and the whole bit will skip - at least that's what i intended...
my approach is kinda cumbersome.. <hides in corner> :)

just post what you think and whether it works

[Peckers]

eh.. wouldn't the very first select return a table with FURNITURENAME, FURNITURENO and a 3rd column filled with the constant 'NO ORDERS' including only items with a FURNITURENO that isn't listed in MY_FURNITUREORDER (=haven't been ordered)?

yep :)


the following select would generate a table (that is joined with the one described above) with FURNITURENAME, FURNITURENO and the 3rd column filled with the constant 'LEAST ORDERED' including items whose FURNITURENO is listed in MY_FURNITUREORDER (=have been ordered) grouped by items that have the same amount of orders (?)

only the furniture(s) with min(count()) of orders will be shown.



SELECT FURNITURENAME, FURNITURENO, 'LEAST ORDERED' AS STATUS
FROM MY_FURNITURE WHERE (FURNITURENO IN
(SELECT FURNITURENO
FROM MY_FURNITUREORDER
GROUP BY FURNITURENO HAVING COUNT(FURNITURENO) =
(SELECT MIN(COUNT(FURNITURENO))
FROM MY_FURNITUREORDER
GROUP BY FURNITURENO))) AND
(COUNT(SELECT FURNITURENO FROM MY_FURNITURE
WHERE FURNITURENO NOT IN
(SELECT FURNITURENO FROM MY_FURNITUREORDER)) IS NULL)

sorry no it doesn't work, group functions (COUNT) can't be run there. good idea though.

It would be nice to be able to use IF-THEN-ELSE in a SELECT statement but I havent figured out how to do that, CASE might work..
Unfornunatery I have sevaral exams coming up so I don't have much time to play around with this. :(
I really appreciate you taking the time to give me some input, thanks!

here is a pic of my query run, in this case I would like only F0000006 to show