April 22, 2014 at 9:05 am
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
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
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
All sorted now. Thank you 🙂
April 23, 2014 at 9:46 pm
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
Change is inevitable... Change for the better is not.
April 24, 2014 at 5:29 am
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