Prev: OPENQUERY
Next: Need help (again) with hierarhyID
From: bob on 21 Apr 2010 15:05
Hi,

Is there a simpler way than a UNION to return a default value of a joined
table for which a corresponding row does not exist?

The following example (not a working one, of course) illustrates what I'm
after. I'd like to return 'N/A' as c2name if there is no matching row in t2

SELECT t1.c1

(CASE t2.t1pk
WHEN NULL THEN 'N/A'
ELSE t2.name END) AS c2name,

FROM t1

LEFT JOIN t2 ON t2.t1pk = t1.pk

Thanks.


From: Jeroen Mostert on 21 Apr 2010 16:07
On 2010-04-21 21:05, bob wrote:
> Is there a simpler way than a UNION to return a default value of a joined
> table for which a corresponding row does not exist?
>
> The following example (not a working one, of course) illustrates what I'm
> after. I'd like to return 'N/A' as c2name if there is no matching row in t2
>
> SELECT t1.c1
>
If you put a comma here...

> (CASE t2.t1pk
> WHEN NULL THEN 'N/A'
> ELSE t2.name END) AS c2name,
>
> FROM t1
>
> LEFT JOIN t2 ON t2.t1pk = t1.pk
>
....then your query is syntactically valid and gives the desired result.

If you need something more complicated, you'll have to be more specific.

--
J.
From: Q on 21 Apr 2010 16:52
When comparing to NULL value, you have to use IS NULL or IS NOT NULL.
In your case, you need to re-write the CASE statement to:
(CASE WHEN t2.t1pk IS NULL THEN 'N/A'
ELSE t2.name END) AS c2name

Another example that I have is:
DECLARE @Emp TABLE (EmpID int, Name varchar(50))
declare @EmpNickName TABLE (EmpID int, NickName varchar(50))

INSERT INTO @Emp VALUES (1,'John T. Thompson')
INSERT INTO @Emp VALUES (2,'Gina Jackson')
INSERT INTO @Emp VALUES (3,'Dan Smith')

INSERT INTO @EmpNickName VALUES (1,'John')
INSERT INTO @EmpNickName VALUES (2,'Gina')

SELECT e.EmpID, e.Name,
(CASE WHEN n.NickName IS NULL THEN 'N/A'
ELSE n.NickName
END) as NickName
FROM @Emp e
LEFT JOIN @EmpNickName n ON n.EmpID = e.EmpID



"Jeroen Mostert" wrote:

> On 2010-04-21 21:05, bob wrote:
> > Is there a simpler way than a UNION to return a default value of a joined
> > table for which a corresponding row does not exist?
> >
> > The following example (not a working one, of course) illustrates what I'm
> > after. I'd like to return 'N/A' as c2name if there is no matching row in t2
> >
> > SELECT t1.c1
> >
> If you put a comma here...
>
> > (CASE t2.t1pk
> > WHEN NULL THEN 'N/A'
> > ELSE t2.name END) AS c2name,
> >
> > FROM t1
> >
> > LEFT JOIN t2 ON t2.t1pk = t1.pk
> >
> ....then your query is syntactically valid and gives the desired result.
>
> If you need something more complicated, you'll have to be more specific.
>
> --
> J.
> .
>
From: Tom Cooper on 21 Apr 2010 20:59
Or just use the COALESCE function, e.g.,

SELECT t1.c1,
COALESCE( t2.t1pk, 'N/A') AS c2name,
FROM t1
LEFT JOIN t2 ON t2.t1pk = t1.pk

Tom

"bob" <robert(a)robert.org> wrote in message
news:uWdDeWY4KHA.3644(a)TK2MSFTNGP06.phx.gbl...
> Hi,
>
> Is there a simpler way than a UNION to return a default value of a joined
> table for which a corresponding row does not exist?
>
> The following example (not a working one, of course) illustrates what I'm
> after. I'd like to return 'N/A' as c2name if there is no matching row in
> t2
>
> SELECT t1.c1
>
> (CASE t2.t1pk
> WHEN NULL THEN 'N/A'
> ELSE t2.name END) AS c2name,
>
> FROM t1
>
> LEFT JOIN t2 ON t2.t1pk = t1.pk
>
> Thanks.
>

From: Jeroen Mostert on 22 Apr 2010 00:57
On 2010-04-21 22:52, Q wrote:
> When comparing to NULL value, you have to use IS NULL or IS NOT NULL.

Wow, I don't know how I managed to miss that.

--
J.
 |  Next  |  Last
Pages: 1 2
Prev: OPENQUERY
Next: Need help (again) with hierarhyID