Solved

How to use if exists in a select statement

Posted on 2008-10-02
4
593 Views
Last Modified: 2010-04-21
I need to figure out a way to indicate when a user has a monitor, a box or both. Here's the query I'm currently using:
select u.user_id,
(SELECT top 1 lm.link_monitor_id
FROM monitor_links lm
WHERE lm.user_id = u.user_id) as monitor,
(SELECT top 1 lb.link_box_id
FROM box_links lb
WHERE lb.user_id = u.user_id) as box

With this query, I get the following results:
user_id monitor_link_id box_link_id
12345   M123                B123
98765   M234                NULL
87654   NULL                B678

I would lke to get the following:
12345   both
98765   monitor
87654   box

I was thinking of using IF EXISTS, but couldn't make it work. Any ideas? Thanks!
0
Comment
Question by:horalia
4 Comments
 
LVL 18

Expert Comment

by:David Robitaille
Comment Utility

use a case
select u.user_id,

case (SELECT 1 FROM monitor_links lm

WHERE lm.user_id = u.user_id) +

(SELECT top 1 lb.link_box_id

when 3 then 'both'

when 2 then 'monitor'

when 1 then 'box'

else 0 then 'nothing' end   as x

FROM box_links lb

WHERE lb.user_id = u.user_id)

Open in new window

0
 
LVL 35

Expert Comment

by:Terry Woods
Comment Utility
I think this should do it:
select distinct u.user_id, "both" equipment

from user_table u inner join monitor_links lm on lm.user_id = u.user_id

                  inner join box_links lb on lb.user_id = u.user_id

union 

select distinct u.user_id, "monitor"

from user_table u inner join monitor_links lm on lm.user_id = u.user_id

                  left join box_links lb on lb.user_id = u.user_id

where lb.user_id is null

union

select distinct u.user_id, "box"

from user_table u left join monitor_links lm on lm.user_id = u.user_id

                  inner join box_links lb on lb.user_id = u.user_id

where lm.user_id is null

Open in new window

0
 
LVL 11

Accepted Solution

by:
yuching earned 500 total points
Comment Utility
try this

SELECT u.user_id,
   (CASE WHEN b.monitor IS NOT NULL AND c.box IS NOT NULL THEN 'both'
               WHEN b.monitor IS NOT NULL THEN 'monitor'
               WHEN c.box IS NOT NULL THEN 'box'
                ELSE 'no match'
     END) As columnB
FROM usertables u
LEFT OUTER JOIN  (
     SELECT lm.user_id, MAX(lm.link_monitor_id) As monitor
     FROM monitor_links lm
      GROUP BY lm.user_id
) b ON b.user_id = u.user_id
LEFT OUTER JOIN (
      SELECT lb.user_id,  lb.link_box_id AS box
      FROM box_links lb
      GROUP BY lb.user_id
) c ON c.user_id = u.user_id
0
 

Author Closing Comment

by:horalia
Comment Utility
Thanks!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now