Post reply

Problem with SQL statement pulling out incorrect data

  • April 22, 2014 at 9:05 am

    #286044

    Hi there folks

    I'm not at all adept at using T-SQL, and I've got a problem with a script that I've written as per below.

    The report pulls out "almost" correct data, but also shows columns with telephone numbers whereas I just want to show either e-mail address or NULL columns.

    The m.notes column holds the actual e-mail address whilst the m.device column holds the device (e.g. telephone (TE), fax (FX), e-mail (EM).

    So, how do I ask for WHERE m.device='EM' or NULL - as this doesn't seem to work!

    Many thanks

    Jon

    SELECT org.organisation_number, org.name, addr.address, addr.town, addr.county,

    addr.postcode, addr.country, org.status, org.std_code, org.telephone, c.label_name, cp.position, m.notes, m.device

    FROM communications m

    RIGHT OUTER JOIN contact_roles cr on m.contact_number = cr.contact_number

    FULL OUTER JOIN contacts co on m.contact_number = co.contact_number

    INNER JOIN organisations org on cr.organisation_number = org.organisation_number

    INNER JOIN contacts c on cr.contact_number = c.contact_number

    INNER JOIN addresses addr on addr.address_number = org.address_number

    INNER JOIN contact_positions cp on cp.contact_number = c.contact_number

    WHERE cr.role In ('MAIN') AND cp.mail = 'Y' AND (org.status In ('FULL','HOLD'))

    ORDER BY org.name

  • April 22, 2014 at 9:17 am

    #1707800

    Hi

    Stab in the dark but I think your after:

    WHERE (m.device='EM' or m.device IS NULL)

    Andy

    ==========================================================================================================================
    A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

  • April 22, 2014 at 9:42 am

    #1707814

    Does Andy's idea work?

    It's not quite clear what you mean. Do you want to show the email if the device is not null or show the email only and NULL for the other columns?

    Andy's idea shows you how to test for two values. You need to use the column name in each side of the AND/OR. You can also use parenthesis to separate out and force logic to be evaluated together.

    I'm guessing you want something like:

    WHERE cr.role In ('MAIN') AND cp.mail = 'Y'

    AND (org.status In ('FULL','HOLD'))

    AND (m.device='EM' or m.device IS NULL)

    But you'll have to spell out your logic a little more if this is not the case.

  • April 23, 2014 at 2:59 am

    #1708001

    All sorted now. Thank you 🙂

  • April 23, 2014 at 9:46 pm

    #1708294

    fuzzyjonclay (4/23/2014)

    All sorted now. Thank you 🙂

    2 way street here. How is it "all sorted now"? What did you end up doing???

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 24, 2014 at 5:29 am

    #1708372

    I used Andy's suggestion (thank you!). I was under the wrong impression that I needed some sort out of FULL OUTER JOIN.

    Thanks guys.

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

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