one column causing duplicate rows - wrong join used?

Hi,
Consider the following result set:
PNID PN_NUMBER Date1 Date2 Status PN_Na
me
========================================
========================
27 2051 08 Sep 1941 NULL Received NULL
28 2143 01 Jan 1945 NULL Accepted NULL
28 2143 01 Jan 1945 NULL Accepted R Anderson
29 2151 NULL NULL Accepted NULL
29 2151 NULL NULL Accepted W Yarwood
30 1579 17 Nov 1925 NULL Received NULL
31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
Here is the sql that returns the above:
SELECT
DISTINCT(PNP.PNID) AS 'PN_ID',
PNP.PNNumber AS 'PN_NUMBER',
CONVERT(VARCHAR(15), PNP.PNDate, 106) AS 'Date1',
CONVERT(VARCHAR(15), PNP.InspectionDate, 106) AS 'Date2',
PNP.PNStatus AS 'Status',
BB.Name AS 'PN_NAME',
FROM
tblPNProperties PNP
LEFT JOIN tblBusinessBoard BB
ON PNP.PNID = BB.PNID
My desired resultset would be to have pnids 28 and 29 to be unique,
however because I am
selecting PN_Name it causes the rows to have duplicates. How would I be
able to obtain my desired resultset? (see below) Is my join correct?
PNID PN_NUMBER Date1 Date2 Status PN_Na
me
========================================
========================
27 2051 08 Sep 1941 NULL Received NULL
28 2143 01 Jan 1945 NULL Accepted R Anderson
29 2151 NULL NULL Accepted W Yarwood
30 1579 17 Nov 1925 NULL Received NULL
31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
Any ideas?
Thanks
qhChange the LEFT JOIN to a JOIN.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Quackhandle" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1133522905.310939.208490@.g49g2000cwa.googlegroups.com...
Hi,
Consider the following result set:
PNID PN_NUMBER Date1 Date2 Status PN_Name
========================================
========================
27 2051 08 Sep 1941 NULL Received NULL
28 2143 01 Jan 1945 NULL Accepted NULL
28 2143 01 Jan 1945 NULL Accepted R Anderson
29 2151 NULL NULL Accepted NULL
29 2151 NULL NULL Accepted W Yarwood
30 1579 17 Nov 1925 NULL Received NULL
31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
Here is the sql that returns the above:
SELECT
DISTINCT(PNP.PNID) AS 'PN_ID',
PNP.PNNumber AS 'PN_NUMBER',
CONVERT(VARCHAR(15), PNP.PNDate, 106) AS 'Date1',
CONVERT(VARCHAR(15), PNP.InspectionDate, 106) AS 'Date2',
PNP.PNStatus AS 'Status',
BB.Name AS 'PN_NAME',
FROM
tblPNProperties PNP
LEFT JOIN tblBusinessBoard BB
ON PNP.PNID = BB.PNID
My desired resultset would be to have pnids 28 and 29 to be unique,
however because I am
selecting PN_Name it causes the rows to have duplicates. How would I be
able to obtain my desired resultset? (see below) Is my join correct?
PNID PN_NUMBER Date1 Date2 Status PN_Name
========================================
========================
27 2051 08 Sep 1941 NULL Received NULL
28 2143 01 Jan 1945 NULL Accepted R Anderson
29 2151 NULL NULL Accepted W Yarwood
30 1579 17 Nov 1925 NULL Received NULL
31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
Any ideas?
Thanks
qh|||Follow-up: If that doesn't fix it, could you please post your DDL for the
two tables + INSERT's of the sample data? We may have to change your query
further.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uTb$rXz9FHA.916@.TK2MSFTNGP10.phx.gbl...
Change the LEFT JOIN to a JOIN.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Quackhandle" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1133522905.310939.208490@.g49g2000cwa.googlegroups.com...
Hi,
Consider the following result set:
PNID PN_NUMBER Date1 Date2 Status PN_Name
========================================
========================
27 2051 08 Sep 1941 NULL Received NULL
28 2143 01 Jan 1945 NULL Accepted NULL
28 2143 01 Jan 1945 NULL Accepted R Anderson
29 2151 NULL NULL Accepted NULL
29 2151 NULL NULL Accepted W Yarwood
30 1579 17 Nov 1925 NULL Received NULL
31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
Here is the sql that returns the above:
SELECT
DISTINCT(PNP.PNID) AS 'PN_ID',
PNP.PNNumber AS 'PN_NUMBER',
CONVERT(VARCHAR(15), PNP.PNDate, 106) AS 'Date1',
CONVERT(VARCHAR(15), PNP.InspectionDate, 106) AS 'Date2',
PNP.PNStatus AS 'Status',
BB.Name AS 'PN_NAME',
FROM
tblPNProperties PNP
LEFT JOIN tblBusinessBoard BB
ON PNP.PNID = BB.PNID
My desired resultset would be to have pnids 28 and 29 to be unique,
however because I am
selecting PN_Name it causes the rows to have duplicates. How would I be
able to obtain my desired resultset? (see below) Is my join correct?
PNID PN_NUMBER Date1 Date2 Status PN_Name
========================================
========================
27 2051 08 Sep 1941 NULL Received NULL
28 2143 01 Jan 1945 NULL Accepted R Anderson
29 2151 NULL NULL Accepted W Yarwood
30 1579 17 Nov 1925 NULL Received NULL
31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
Any ideas?
Thanks
qh|||Hi Tom,
thanks for both replies. Unfortunately using JOIN did not work. when
I type the following
select * from tblPNProperties
where pnid = '28'
I get 1 row
select * from tblBusinessboard
where pnid = '28'
however here I get two rows
I have a hunch that the data is incorrect.
Back to the drawing board
cheers
qh|||On 2 Dec 2005 03:28:25 -0800, Quackhandle wrote:

>Hi,
>Consider the following result set:
> PNID PN_NUMBER Date1 Date2 Status PN_Na
me
> ========================================
========================
>27 2051 08 Sep 1941 NULL Received NULL
>28 2143 01 Jan 1945 NULL Accepted NULL
>28 2143 01 Jan 1945 NULL Accepted R Anderson
>29 2151 NULL NULL Accepted NULL
>29 2151 NULL NULL Accepted W Yarwood
>30 1579 17 Nov 1925 NULL Received NULL
>31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
>
>Here is the sql that returns the above:
>SELECT
> DISTINCT(PNP.PNID) AS 'PN_ID',
> PNP.PNNumber AS 'PN_NUMBER',
> CONVERT(VARCHAR(15), PNP.PNDate, 106) AS 'Date1',
> CONVERT(VARCHAR(15), PNP.InspectionDate, 106) AS 'Date2',
> PNP.PNStatus AS 'Status',
> BB.Name AS 'PN_NAME',
>FROM
> tblPNProperties PNP
> LEFT JOIN tblBusinessBoard BB
> ON PNP.PNID = BB.PNID
>My desired resultset would be to have pnids 28 and 29 to be unique,
>however because I am
>selecting PN_Name it causes the rows to have duplicates. How would I be
>able to obtain my desired resultset? (see below) Is my join correct?
>
> PNID PN_NUMBER Date1 Date2 Status PN_Na
me
> ========================================
========================
>27 2051 08 Sep 1941 NULL Received NULL
>28 2143 01 Jan 1945 NULL Accepted R Anderson
>29 2151 NULL NULL Accepted W Yarwood
>30 1579 17 Nov 1925 NULL Received NULL
>31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
>
>Any ideas?
>
>Thanks
>qh
Hi qh,
Since you didn't post CREATE TABLE and INSERT statements, here's a wild
and completely untested guess:
SELECT
PNP.PNID AS 'PN_ID',
PNP.PNNumber AS 'PN_NUMBER',
CONVERT(VARCHAR(15), PNP.PNDate, 106) AS 'Date1',
CONVERT(VARCHAR(15), PNP.InspectionDate, 106) AS 'Date2',
PNP.PNStatus AS 'Status',
MAX(BB.Name) AS 'PN_NAME'
FROM
tblPNProperties PNP
LEFT JOIN tblBusinessBoard BB
ON PNP.PNID = BB.PNID
GROUP BY
PNP.PNID,
PNP.PNNumber,
PNP.PNDate,
PNP.InspectionDate,
PNP.PNStatus
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)