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)

oledb to ibm db2 - no tables?

Hi.
I am upgrading a datawarehouse from sql2000 to sql2005.
Unfortunately, there is something wrong when I try to get the list of
available tables/views to import from ibm as400.
It worked fine on sql2000 (I was using "iSeries Access ODBC Driver") but
there is no such driver available on sql2005. The only available driver is
the "IBM DB2 UDB for iSeries OLE DB Provider" which cannot display the list
of all the available tables / libraries on the remote system. The message
is: "The source database you have selected contains no visible tables or
views." On sql2000 I was able to fetch the list of all the available tables
in all the libraries, put a tick against some of them and just import them
to the sql server.
I noticed that I can use the .Net driver for ODBC which allows me to import
data from any odbc source (defined in control panel -> administrative tools
-> odbc...), but this approach requires typing the whole sql statement for
each source table. This could be OK for a single table, but there are more
than 50 tables I need to import and writing separate sql statement for each
of them is something I would like to avoid.
Is there anybody with good experience regarding this issue? Please help...
--
PLI'm succesfuly accesing AS/400 files via OLEDB from SQL Server 2005 SP2.
When selecting from libraries files I simply do " SELECT * FROM
AS400OLE.AS400.LIBRARY.FILE " and it works fine (IMHO faster than ODBC too!)
Here is my linked server script, hope it will help you:
(our AS/400 system name is.. AS400 :-) ).
/****** Object: LinkedServer [AS400OLE] Script Date: 04/16/2007 16:58:00
******/
EXEC master.dbo.sp_addlinkedserver @.server = N'AS400OLE',
@.srvproduct=N'AS400', @.provider=N'IBMDA400', @.datasrc=N'AS400',
@.catalog=N'AS400'
/* For security reasons the linked server remote logins password is changed
with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname=N'AS400OLE',@.useself=N'False',@.locallogin=NULL,@.rmtuser=N'AS400USERID',@.rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'collation
compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'data
access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'dist',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'pub',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'rpc',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'rpc out',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'sub',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'connect
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'collation
name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'lazy schema
validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'query
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'use remote
collation', @.optvalue=N'true'
"Piotr Lipski" <piotr.lipski@.nie.p0czta.spamuj.bo.onet.w.ryj.pl> wrote in
message news:1rjimvs4uyts7$.3ctxiz7tm0uv.dlg@.40tude.net...
> Hi.
> I am upgrading a datawarehouse from sql2000 to sql2005.
> Unfortunately, there is something wrong when I try to get the list of
> available tables/views to import from ibm as400.
> It worked fine on sql2000 (I was using "iSeries Access ODBC Driver") but
> there is no such driver available on sql2005. The only available driver is
> the "IBM DB2 UDB for iSeries OLE DB Provider" which cannot display the
> list
> of all the available tables / libraries on the remote system. The message
> is: "The source database you have selected contains no visible tables or
> views." On sql2000 I was able to fetch the list of all the available
> tables
> in all the libraries, put a tick against some of them and just import them
> to the sql server.
> I noticed that I can use the .Net driver for ODBC which allows me to
> import
> data from any odbc source (defined in control panel -> administrative
> tools
> -> odbc...), but this approach requires typing the whole sql statement for
> each source table. This could be OK for a single table, but there are more
> than 50 tables I need to import and writing separate sql statement for
> each
> of them is something I would like to avoid.
> Is there anybody with good experience regarding this issue? Please help...
> --
> PL|||On Mon, 16 Apr 2007 17:00:42 -0400, Rafael Lenartowicz wrote:
Thanks for hint. In the meantime I've re-installed the whole as400 software
suite, choosing "full" option this time - and it definitely works better.
At least, I can get the list of all the tables on remote system.
Unfortunately, I still cannot get the full list of all the columns in a
particular table - I have to run "select * from [as400table]" to do it.
Anyway, I'm happy enough. I can import data (and schedule the import
packages). Next step will be learning how to fire ssis packages on demand.
Cheers,
PL
> I'm succesfuly accesing AS/400 files via OLEDB from SQL Server 2005 SP2.
> When selecting from libraries files I simply do " SELECT * FROM
> AS400OLE.AS400.LIBRARY.FILE " and it works fine (IMHO faster than ODBC too!)
> Here is my linked server script, hope it will help you:
[...]

oledb to ibm db2 - no tables?

Hi.
I am upgrading a datawarehouse from sql2000 to sql2005.
Unfortunately, there is something wrong when I try to get the list of
available tables/views to import from ibm as400.
It worked fine on sql2000 (I was using "iSeries Access ODBC Driver") but
there is no such driver available on sql2005. The only available driver is
the "IBM DB2 UDB for iSeries OLE DB Provider" which cannot display the list
of all the available tables / libraries on the remote system. The message
is: "The source database you have selected contains no visible tables or
views." On sql2000 I was able to fetch the list of all the available tables
in all the libraries, put a tick against some of them and just import them
to the sql server.
I noticed that I can use the .Net driver for ODBC which allows me to import
data from any odbc source (defined in control panel -> administrative tools
-> odbc...), but this approach requires typing the whole sql statement for
each source table. This could be OK for a single table, but there are more
than 50 tables I need to import and writing separate sql statement for each
of them is something I would like to avoid.
Is there anybody with good experience regarding this issue? Please help...
PLI'm succesfuly accesing AS/400 files via OLEDB from SQL Server 2005 SP2.
When selecting from libraries files I simply do " SELECT * FROM
AS400OLE.AS400.LIBRARY.FILE " and it works fine (IMHO faster than ODBC too!)
Here is my linked server script, hope it will help you:
(our AS/400 system name is.. AS400 :-) ).
/****** Object: LinkedServer [AS400OLE] Script Date: 04/16/2007 16:58:00
******/
EXEC master.dbo.sp_addlinkedserver @.server = N'AS400OLE',
@.srvproduct=N'AS400', @.provider=N'IBMDA400', @.datasrc=N'AS400',
@.catalog=N'AS400'
/* For security reasons the linked server remote logins password is changed
with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname=N'AS400OLE',@.useself=N'False
',@.locallogin=NULL,@.rmtuser=N'AS400U
SERID',@.rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'collation
compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'data
access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'dist',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'pub',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'rpc',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'rpc out',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'sub',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'connect
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'collation
name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'lazy schema
validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'query
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'use remote
collation', @.optvalue=N'true'
"Piotr Lipski" <piotr.lipski@.nie.p0czta.spamuj.bo.onet.w.ryj.pl> wrote in
message news:1rjimvs4uyts7$.3ctxiz7tm0uv.dlg@.40tude.net...
> Hi.
> I am upgrading a datawarehouse from sql2000 to sql2005.
> Unfortunately, there is something wrong when I try to get the list of
> available tables/views to import from ibm as400.
> It worked fine on sql2000 (I was using "iSeries Access ODBC Driver") but
> there is no such driver available on sql2005. The only available driver is
> the "IBM DB2 UDB for iSeries OLE DB Provider" which cannot display the
> list
> of all the available tables / libraries on the remote system. The message
> is: "The source database you have selected contains no visible tables or
> views." On sql2000 I was able to fetch the list of all the available
> tables
> in all the libraries, put a tick against some of them and just import them
> to the sql server.
> I noticed that I can use the .Net driver for ODBC which allows me to
> import
> data from any odbc source (defined in control panel -> administrative
> tools
> -> odbc...), but this approach requires typing the whole sql statement for
> each source table. This could be OK for a single table, but there are more
> than 50 tables I need to import and writing separate sql statement for
> each
> of them is something I would like to avoid.
> Is there anybody with good experience regarding this issue? Please help...
> --
> PL|||On Mon, 16 Apr 2007 17:00:42 -0400, Rafael Lenartowicz wrote:
Thanks for hint. In the meantime I've re-installed the whole as400 software
suite, choosing "full" option this time - and it definitely works better.
At least, I can get the list of all the tables on remote system.
Unfortunately, I still cannot get the full list of all the columns in a
particular table - I have to run "select * from [as400table]" to do it.
Anyway, I'm happy enough. I can import data (and schedule the import
packages). Next step will be learning how to fire ssis packages on demand.
Cheers,
PL

> I'm succesfuly accesing AS/400 files via OLEDB from SQL Server 2005 SP2.
> When selecting from libraries files I simply do " SELECT * FROM
> AS400OLE.AS400.LIBRARY.FILE " and it works fine (IMHO faster than ODBC too
!)
> Here is my linked server script, hope it will help you:
[...]sql

OLEDB source - Use table or select only columns needed.

Hi All,

With the OLEDB source, is it wrong to use a table / view as a source and only check the columns required or is it beneficial to write a select col1, col2 etc etc as a SQL command?

I cannot see any difference in performance between the two.

Thanks.

Always always always use a SQL command so as to avoid the situation documented here: http://blogs.conchango.com/jamiethomson/archive/2006/02/21/2930.aspx I can't stress this enough.

Also check #4 here: http://blogs.conchango.com/jamiethomson/archive/2006/01/05/2554.aspx Basically, only pull in the data that you need otherwise performance will suffer.

-Jamie

|||Thanks Jamie but...

I would still like to know the underlying reason it is bad :)

Accepted that Select * is bad due to many reasons but:
My "tables" are views which themselves only select columns required for the data flow and nothing more.

Basically I am being lazy - I write the columns out in the view and don't _really_ want to write them again in SSIS :)

From a performance point of view, the above method is exactly the same either way. I cannot and have not seen what you described.

Will play around some more and try find a reason (unless someone wants to save us the trouble....)|||

Well if nothing else I would do it in the interests of best practice. And also cos I'm picky - I hate seeing a selected table rather than a SQL statement :)

-Jamie

|||

Jamie Thomson wrote:

Well if nothing else I would do it in the interests of best practice. And also cos I'm picky - I hate seeing a selected table rather than a SQL statement :)

-Jamie

A counter to that is I hate seeing any form of SQL in SSIS. Rather have the logic in a view / proc or just pull from the table. Make life easier when looking for bugs.
(yes, you could make a rule such as "do not use anything more that Select *" :)|||

Let's agree to disagree! :)

-J