469,610 Members | 2,081 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,610 developers. It's quick & easy.

Select JOIN problem

The query:
SELECT BTbl.PKey, BTbl.Result
FROM BTbl INNER JOIN
ATbl ON BTbl.PKey = ATbl.PKey
WHERE (ATbl.Status = 'DROPPED') AND (BTbl.Result <> 'RESOLVED')

Returns no rows.
If I do:
SELECT BTbl.PKey, BTbl.Result
FROM BTbl INNER JOIN
ATbl ON BTbl.PKey = ATbl.PKey
WHERE (ATbl.Status = 'DROPPED')

Returns:
PKey Result
125
127 RESOLVED

I want the first query to return the row with PKey: 125 because it's
result field does not equal 'RESOLVED'
Any ideas what I'm doing wrong?

My tables:

CREATE TABLE [dbo].[ATbl] (
[PKey] [int] NOT NULL ,
[Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[BTbl] (
[PKey] [int] NOT NULL ,
[Result] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Jul 23 '05 #1
2 1311

<sh******@cs.fsu.edu> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
The query:
SELECT BTbl.PKey, BTbl.Result
FROM BTbl INNER JOIN
ATbl ON BTbl.PKey = ATbl.PKey
WHERE (ATbl.Status = 'DROPPED') AND (BTbl.Result <> 'RESOLVED')

Returns no rows.
If I do:
SELECT BTbl.PKey, BTbl.Result
FROM BTbl INNER JOIN
ATbl ON BTbl.PKey = ATbl.PKey
WHERE (ATbl.Status = 'DROPPED')

Returns:
PKey Result
125
127 RESOLVED

I want the first query to return the row with PKey: 125 because it's
result field does not equal 'RESOLVED'
Any ideas what I'm doing wrong?

My tables:

CREATE TABLE [dbo].[ATbl] (
[PKey] [int] NOT NULL ,
[Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[BTbl] (
[PKey] [int] NOT NULL ,
[Result] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO


What exactly is BTbl.Result where BTbl.PKey is 125? If it's a NULL, then
this behaviour would be correct; if it's an empty string, then your query
should work. If you do have NULLs in the column, you could try this query
instead:

SELECT BTbl.PKey, BTbl.Result
FROM BTbl INNER JOIN
ATbl ON BTbl.PKey = ATbl.PKey
WHERE (ATbl.Status = 'DROPPED') AND (coalesce(BTbl.Result, '') <>
'RESOLVED')

If this doesn't help, I suggest you post INSERTs to add some sample data to
your tables so that someone else can reproduce what you're seeing.

Simon
Jul 23 '05 #2
Sweet.. that works.

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Ian T | last post: by
3 posts views Thread by Tcs | last post: by
1 post views Thread by Neil H | last post: by
4 posts views Thread by Jean-Claude | last post: by
22 posts views Thread by MP | last post: by
6 posts views Thread by Jon Bilbao | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.