|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
one column causing duplicate rows - wrong join used?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 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" <quackhandle1***@yahoo.co.uk> wrote in message Consider the following result set:news:1133522905.310939.208490@g49g2000cwa.googlegroups.com... Hi, 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 Change the LEFT JOIN to a JOIN.news:uTb$rXz9FHA.916@TK2MSFTNGP10.phx.gbl... -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinpub.com .. "Quackhandle" <quackhandle1***@yahoo.co.uk> wrote in message Consider the following result set:news:1133522905.310939.208490@g49g2000cwa.googlegroups.com... Hi, 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:
Show quote >Hi, Hi qh,> >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 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)  
Other interesting topics
|
|||||||||||||||||||||||
