how to list monitors without pcs. 1

[arravalli12]

I have a table which has equ_id,user_id,room_no,srl_no in inventory table.
I have to list users name which has monitors without pc's. Some people have 2 pcs and one monitor.
equ_id = 4 is monitor, 1 is pc.

Example:

ROOM USER Equ_id srlNo
ROOM 110 DNEL 1 VT7500G
ROOM 110 DNEL 4 7377XC
ROOM 110 DNEL 3 Z2949 ROOM MIS DNEL 4 FX7000

 

[PHV]

You may try something like this:
SELECT * FROM (
SELECT room_no, user_id,
SUM(CASE WHEN equ_id = 4 THEN 1 ELSE 0 END) monitor,
SUM(CASE WHEN equ_id = 1 THEN 1 ELSE 0 END) pc
FROM yourTable
WHERE equ_id IN (1,4)
GROUP BY room_no, user_id
) A WHERE A.monitor > A.pc

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[arravalli12]

That query was just quick, great and works fine. Thanks a lot for that. I could not have done on my own..