May 31, 2005 at 10:42 am
Hi there,
I want to make a simple statement that searches for a value in the database and where a record doesnt exist, instead displays "None listed"
EG- I have contact listed in one tbale, and their phone numbers in another. They are linked by a contactid. Where the phone number (s) exists, display it. IF not, display "None".
Any suggestions?
Many thanks,
Alex
May 31, 2005 at 10:46 am
Look up CASE in Books Online. Sounds like exactly what you are looking for. Ex.
USE pubs
GO
SELECT 'Price Category' =
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN 'Very Reasonable Title'
WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
ELSE 'Expensive book!'
END,
CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
May 31, 2005 at 10:52 am
DECLARE @Contact TABLE
(
ContactID INT IDENTITY,
ContactName VARCHAR(100)
)
INSERT @Contact VALUES ('Contact 1')
INSERT @Contact VALUES ('Contact 2')
INSERT @Contact VALUES ('Contact 3')
INSERT @Contact VALUES ('Contact 4')
INSERT @Contact VALUES ('Contact 5')
DECLARE @Phone TABLE
(
ContactID INT,
Phone VARCHAR(15)
)
INSERT @Phone VALUES (1, '000-000-0000')
INSERT @Phone VALUES (2, '222-222-2222')
INSERT @Phone VALUES (5, '123-456-7890')
SELECT ContactName,
COALESCE(Phone, 'None')
FROM
@Contact A
LEFT OUTER JOIN
@Phone B
ON A.ContactID = B.ContactID
Regards,
gova
May 31, 2005 at 10:55 am
What you need is a left Join depending on the Relationships between the tables it should be something like:
Select c.Contact, Isnull(p.PhoneNo, "None") As Phone
from Contacts c Left join Phones p on c.ContactID = p.ContactID
* Noel
May 31, 2005 at 11:05 am
Cheers Chirs, Govind and Noel. Ive been having to write SQL in this really cr4ppy basic report writing tool that doesn't allow any procedural stuff.
Noel's suggestion worked a treat mind, realyl simple and effective- the best way!
Thanks again,
Alex
May 31, 2005 at 11:15 am
Sorry.. just noticed the folllwing:
SELECT ISNULL (EntityPhoneNumbers.PhoneNumber, 'None')
FROM Entity
LEFT JOIN EntityPhoneNumbers ON Entity.EntityID = EntityPhoneNumbers.EntityID
WHERE Entity.EntityID = ||EntID|| (this is a value passed to it)
works fine n dandy but
SELECT ISNULL (EntityPhoneNumbers.PhoneNumber, 'None')
FROM Entity
LEFT JOIN EntityPhoneNumbers ON Entity.EntityID = EntityPhoneNumbers.EntityID
WHERE Entity.EntityID = ||EntID||
AND EntityPhoneNumbers.PhoneTypeID = 1
Doesn't! Surely you are allowed more than one where clause on a left join?
May 31, 2005 at 12:17 pm
Can you give some sample data as per my previous reply in this thread.
Regards,
gova
May 31, 2005 at 12:22 pm
If what you are trying to avoid is a Filtering on the overall result just move that extra clause to the join like:
SELECT ISNULL (EntityPhoneNumbers.PhoneNumber, 'None')
FROM Entity
LEFT JOIN EntityPhoneNumbers ON Entity.EntityID = EntityPhoneNumbers.EntityID AND EntityPhoneNumbers.PhoneTypeID = 1
WHERE Entity.EntityID = ||EntID||
hth
* Noel
June 1, 2005 at 3:11 am
Berilliant guys, suggestions worked a treat!
June 1, 2005 at 4:07 am
AND EntityPhoneNumbers.PhoneTypeID = 1
Since this is a column in the "unpreserved" table, it will turn the LEFT JOIN into an INNER JOIN and therefore doesn't yield the result you expect.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply