Using IF EXISTS in a select statement

Question

Post reply

Using IF EXISTS in a select statement

paul 69259

Hall of Fame

Points: 3068

February 8, 2021 at 10:23 am

#3842375

Hi All,

I've got a couple of tables and I'm trying to use IF EXISTS to decide if I need to get details another table or just carry on with the main table.

A bit of background information, there's a jobs table that has details of deliveries (from point A to point B), there's also a drops table which has details of other drops that could be completed on the way between points A and B. The tables are linked via the job iD. If there are drops in the drops table for the job I want to get the 'type', 'podname' and 'pod date' from the drops table, if no drops then I just want to carry on through the jobs table getting the details I need from there.

This is a version of what I have so far but it only seems to be giving me data from the drops table and nothing from the jobs table, it doesn't seem to go into the ELSE part......

Thanks in advance

Paul.

IF exists (select dropno from drops inner join jobs on jobs.jobid = drops.jobid)
BEGIN
     SELECT d.dropno as [Drop No.],
     d.type as Type,
     d.Company as Company,
     d.PostCode as PostCode,
     d.podname as [POD Name],
     d.poddateandtime as [POD Date]
     FROM DROPS D INNER JOIN JOBS J
     ON J.JOBID = D.JOBID
END
ELSE
BEGIN
    SELECT 
    j.jobnumber as [UWL Reference],
    isnull(convert(varchar(10),j.coldateandtime,103),'') AS [Collection Date],
    isnull(convert(varchar(10),j.deldateandtime,103),'') AS [Requested Delivery Date],
    isnull(j.delcompany,'') as [Consignee/Recipient],
    isnull(j.jobreference,'') as [Your Reference],
    isnull(j.consignmentno,'') as [Tracking Reference],
    s.status as [Transit Update/Status],
    isnull(j.podname,'-') as [POD Name],
    isnull(convert(varchar(10),j.poddateandtime,103),'') as [POD Date],
    isnull(j.specialinsts,'') as [Special Instructions],
    j.coldateandtime,
    c.customernumber
    from jobs j
        left outer join status s
        ON
        j.statusid = s.statusid
        left outer join customer c
       on j.customerid = c.customerid
END

This topic was modified 3 years, 10 months ago by paul 69259.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 1

In your first query ( "then" part ) you only select columns of the "DROPS " table.

of course, your "ELSE" part will only get executed when it does not find any "from drops inner join jobs on jobs.jobid = drops.jobid"

So, once you have a matching query, it will keep on finding it ( because there is no where clause filtering on a non-join column ) !

( unless you are doing maintenance by e.g. removing processed rows )

Also keep in mind you are returning two different sets ! ( different columns ! ), which may cause difficulties in the processing app.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

GroverVivek SSCommitted Points: 1676 More actions · Answer 2

You can write a single query by doing LEFT JOIN of jobs table with status, and drops table.

For job without any drops, you will get a single rows.
For job with drops, you will get 1 or more than 1 rows.

In select clause, created as many as columns you want to do. Let me know if it is helping!

This reply was modified 3 years, 10 months ago by GroverVivek.

Regards
VG

paul 69259 Hall of Fame Points: 3068 More actions · Answer 3

Hi GroverVivek,

Thank you. I realised I was going about it the wrong way and have now resolved my issue.

Thank you for taking the time to help.

Kind regards

Paul.

GroverVivek SSCommitted Points: 1676 More actions · Answer 4

GroverVivek

SSCommitted

Points: 1676

February 15, 2021 at 1:08 pm

#3845197

Good to know! Thanks Paul

Regards
VG