Help with SQL query

Hi,

Let's say I have a Customer table and an Order table. The latter is linked to the former through foreign key CustomerID. Now, I want to create a SQL statement which, given a CustomerID, returns the corresponding row in Customer table PLUS a field indicating the total number of orders this particular customer has. How can I achieve this with a single SQL statement?

Thanks in advance

--USE Northwind

SELECT Customers.CustomerID, SUM(Orders.OrderID) AS TotalOrders

FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID

GROUP BY Customers.CustomerID

|||Thanks, this is exactly what I want, except I need to change the aggregate function to Count instead of Sum.

Help with NOT EXISTS query

Newbie here...looking for help with a NOT EXISTS query or suggestions for a
better method.
I have 2 linked tables: CUSTOMERS and ADDRESSES
common fields are CUS_NO and ADR_CD
I need to find records where an address code (ADR_CD) entered into CUSTOMERS
does not have that same ADR_CD existing in the ADRESSES table.

Example:
CUS_NO = 12345
ADR_CD = Ohio01

If the combination of cus_no 12345 and Ohio01 does not exist in the
ADDRESSES table, I need to find them.

Thanks in advance.RDRaider wrote:
> Newbie here...looking for help with a NOT EXISTS query or suggestions for a
> better method.
> I have 2 linked tables: CUSTOMERS and ADDRESSES
> common fields are CUS_NO and ADR_CD
> I need to find records where an address code (ADR_CD) entered into CUSTOMERS
> does not have that same ADR_CD existing in the ADRESSES table.
> Example:
> CUS_NO = 12345
> ADR_CD = Ohio01
> If the combination of cus_no 12345 and Ohio01 does not exist in the
> ADDRESSES table, I need to find them.
> Thanks in advance.

SELECT <select list>
FROM Customers c
WHERE NOT EXISTS (SELECT *
FROM Addresses a
WHERE c.Cus_No = a.Cust_No
AND c.ADR_CD = a.ADR_CD)

Zach|||On Mon, 13 Dec 2004 20:17:45 GMT, RDRaider wrote:

> Newbie here...looking for help with a NOT EXISTS query or suggestions for a
> better method.
> I have 2 linked tables: CUSTOMERS and ADDRESSES
> common fields are CUS_NO and ADR_CD
> I need to find records where an address code (ADR_CD) entered into CUSTOMERS
> does not have that same ADR_CD existing in the ADRESSES table.
> Example:
> CUS_NO = 12345
> ADR_CD = Ohio01
> If the combination of cus_no 12345 and Ohio01 does not exist in the
> ADDRESSES table, I need to find them.
> Thanks in advance.

SELECT Customers.cus_no, Customers.adr_cd
FROM Customers
WHERE NOT EXISTS
(SELECT *
FROM ADDRESSES
WHERE Customers.cus_no = ADDRESSES.cus_no
AND Customers.adr_cd = Addresses.adr_cd )

Alternative method:

SELECT Customers.cus_no, Customers.adr_cd
FROM Customers
LEFT JOIN Addresses
ON Customers.cus_no = Addresses.cus_no
AND Customers.adr_cd = Addresses.adr_cd
WHERE Addresses.cus_no IS NULL|||Thanks for the quick reply. It works! I don't know why I couldn't get the
same results, need to hit the books I guess.

"nib" <individual_news@.nibsworld.com> wrote in message
news:326chiF3i7ns1U1@.individual.net...
> RDRaider wrote:
>> Newbie here...looking for help with a NOT EXISTS query or suggestions for
>> a better method.
>> I have 2 linked tables: CUSTOMERS and ADDRESSES
>> common fields are CUS_NO and ADR_CD
>> I need to find records where an address code (ADR_CD) entered into
>> CUSTOMERS does not have that same ADR_CD existing in the ADRESSES table.
>>
>> Example:
>> CUS_NO = 12345
>> ADR_CD = Ohio01
>>
>> If the combination of cus_no 12345 and Ohio01 does not exist in the
>> ADDRESSES table, I need to find them.
>>
>> Thanks in advance.
> SELECT <select list>
> FROM Customers c
> WHERE NOT EXISTS (SELECT *
> FROM Addresses a
> WHERE c.Cus_No = a.Cust_No
> AND c.ADR_CD = a.ADR_CD)
> Zach

Help with linked servers

Hello,
I have two servers db1 and db2. I have created a linked server in db2 to
point to db1. It works great from the server db2 via query analyzer. I can
do a select * from db1.dbname.dbo.tablename. However I get the message
"Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection."
when I try the run the same query from a XP Pro box which is not hosting one
of the db servers. The dbservers are running w2k3 and it's sql2k. Thanks in
advance.
JakeYou do not have Kerberos delegation configured properly. Check out the
following KB article for the details.
http://support.microsoft.com/default.aspx?scid=kb;en-us;319723
Sincerely,
Anthony Thomas
"Jake" <rondican@.hotmail.com> wrote in message
news:OUSaF$2yEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have two servers db1 and db2. I have created a linked server in db2
to
> point to db1. It works great from the server db2 via query analyzer. I can
> do a select * from db1.dbname.dbo.tablename. However I get the message
> "Server: Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection."
> when I try the run the same query from a XP Pro box which is not hosting
one
> of the db servers. The dbservers are running w2k3 and it's sql2k. Thanks
in
> advance.
> Jake
>

Help with Linked Servers

Hi Everyone,

I am developing a reporting database server that will use multiple data sources to construct its query (SQL Server 2005, Oracle 9i, and DB2). I successfully linked all 3 databases to my SQL Server 2005 and I am ready to create the report queries. I would like to know what is the best method to do this. Should I refer to the linked servers by four part naming or use a OPENQUERY query. When using a OPENQUERY, you cannot specify parameters to pass to the linked server which makes me question its performance. Can someone please suggest the best way to do this?

Thank you in advance

-Sam

The 'best' method is most likely the four part naming. It allows you to create more robust queries.

OPENQUERY is best used when the remote server is rarely used and for security (or other) reasons, having a 'linked server' is not appropriate. If the remote server is frequently used, then, by all means, add it as a linked server and use the four part names.

Help with linked servers

Hello,
I have two servers db1 and db2. I have created a linked server in db2 to
point to db1. It works great from the server db2 via query analyzer. I can
do a select * from db1.dbname.dbo.tablename. However I get the message
"Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection."
when I try the run the same query from a XP Pro box which is not hosting one
of the db servers. The dbservers are running w2k3 and it's sql2k. Thanks in
advance.
Jake
You do not have Kerberos delegation configured properly. Check out the
following KB article for the details.
http://support.microsoft.com/default...b;en-us;319723
Sincerely,
Anthony Thomas

"Jake" <rondican@.hotmail.com> wrote in message
news:OUSaF$2yEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have two servers db1 and db2. I have created a linked server in db2
to
> point to db1. It works great from the server db2 via query analyzer. I can
> do a select * from db1.dbname.dbo.tablename. However I get the message
> "Server: Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection."
> when I try the run the same query from a XP Pro box which is not hosting
one
> of the db servers. The dbservers are running w2k3 and it's sql2k. Thanks
in
> advance.
> Jake
>

Help with linked servers

Hello,
I have two servers db1 and db2. I have created a linked server in DB2 to
point to db1. It works great from the server DB2 via query analyzer. I can
do a select * from db1.dbname.dbo.tablename. However I get the message
"Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection."
when I try the run the same query from a XP Pro box which is not hosting one
of the db servers. The dbservers are running w2k3 and it's sql2k. Thanks in
advance.
JakeYou do not have Kerberos delegation configured properly. Check out the
following KB article for the details.
http://support.microsoft.com/defaul...kb;en-us;319723
Sincerely,
Anthony Thomas
"Jake" <rondican@.hotmail.com> wrote in message
news:OUSaF$2yEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have two servers db1 and db2. I have created a linked server in db2
to
> point to db1. It works great from the server DB2 via query analyzer. I can
> do a select * from db1.dbname.dbo.tablename. However I get the message
> "Server: Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection."
> when I try the run the same query from a XP Pro box which is not hosting
one
> of the db servers. The dbservers are running w2k3 and it's sql2k. Thanks
in
> advance.
> Jake
>

Help with linked server query

I have two tables in a linked server that I am trying to accomplish the following:

Server is called Medic, schema is informix, catalog is v001
table names are findet and findhdr

1) join indices (patkey and invno)
2) distinct count (finhdr.patid)
3) sum currency(findet.amount)
4) within a date range(findet.ar_date)
5) having specific criteria (findet.proccode = "99201 thru 99205")

Any help is greatly appreciated!

JTremember,in SQL Server,Microsoft using four-section to describe the database object.It's [server].[database].[owner].[object_name].
example,linkserver Access,table name is MyTable,u must write [server_access]...[MyTable].
link server oracle,user is scott,table is employee,u must write [server_oracle].[].[scott].[employee].

I have no knowledge about informix.But I think u can know how to describe ur table according my introduction,Right?|||Originally posted by enhydraboy
remember,in SQL Server,Microsoft using four-section to describe the database object.It's [server].[database].[owner].[object_name].
example,linkserver Access,table name is MyTable,u must write [server_access]...[MyTable].
link server oracle,user is scott,table is employee,u must write [server_oracle].[].[scott].[employee].

I have no knowledge about informix.But I think u can know how to describe ur table according my introduction,Right?

Thank you for the response. It is not what I was looking for. If anyone else can help, I would appreciate it.

help with linked server on sql2000

hello,
I'm trying to create a linked server from an SQL2000 to a Unify ELS
(very old, odbc is version 1) database on SCO unix.
The odbc driver is old but it works fine when used by applications for
creating reports.
What I want to do is make a linked server from the SCO box to the win2k
box. So, I'm using enterprise manager to create the linked server.
The DSN of the unify database is CORE_OFFICE1

I'm getting 'Error 7399: OLE DB provider 'MSDASQL' reported an error.
Datasource name not found and no default driver specified.

When I create the linked server I chose the OLE DB for ODBC drivers.
I put 'CORE_OFFICE1' for the Data Source name. I also setup the
security with the name of a unix account for login/password.
What else should I have?

I'm new at this linked server stuff!

Product Name:
Data Source:
Provider String:
Location:
Catalog:

Thanks,

Oskarpheonix1t <pheonix1tAThoustonDOTrrDOTcom@.com.com> wrote in message news:<vvVTc.3077$np.309@.newssvr22.news.prodigy.com>...
> hello,
> I'm trying to create a linked server from an SQL2000 to a Unify ELS
> (very old, odbc is version 1) database on SCO unix.
> The odbc driver is old but it works fine when used by applications for
> creating reports.
> What I want to do is make a linked server from the SCO box to the win2k
> box. So, I'm using enterprise manager to create the linked server.
> The DSN of the unify database is CORE_OFFICE1
> I'm getting 'Error 7399: OLE DB provider 'MSDASQL' reported an error.
> Datasource name not found and no default driver specified.
> When I create the linked server I chose the OLE DB for ODBC drivers.
> I put 'CORE_OFFICE1' for the Data Source name. I also setup the
> security with the name of a unix account for login/password.
> What else should I have?
> I'm new at this linked server stuff!
> Product Name:
> Data Source:
> Provider String:
> Location:
> Catalog:
> Thanks,
> Oskar

At the risk of asking a silly question, have you created and tested
the DSN successfully on the MSSQL server itself, and is the DSN a
system DSN? Assuming it works OK from Control Panel, then I would try
adding the linked server using Query Analyzer:

EXEC sp_addlinkedserver
@.server = 'Unify',
@.provider = 'MSDASQL',
@.datasrc = 'CORE_OFFICE1'

Simon|||Simon Hayes wrote:

> pheonix1t <pheonix1tAThoustonDOTrrDOTcom@.com.com> wrote in message news:<vvVTc.3077$np.309@.newssvr22.news.prodigy.com>...
>>hello,
>>I'm trying to create a linked server from an SQL2000 to a Unify ELS
>>(very old, odbc is version 1) database on SCO unix.
>>The odbc driver is old but it works fine when used by applications for
>>creating reports.
>>What I want to do is make a linked server from the SCO box to the win2k
>>box. So, I'm using enterprise manager to create the linked server.
>>The DSN of the unify database is CORE_OFFICE1
>>
>>I'm getting 'Error 7399: OLE DB provider 'MSDASQL' reported an error.
>>Datasource name not found and no default driver specified.
>>
>>When I create the linked server I chose the OLE DB for ODBC drivers.
>>I put 'CORE_OFFICE1' for the Data Source name. I also setup the
>>security with the name of a unix account for login/password.
>>What else should I have?
>>
>>I'm new at this linked server stuff!
>>
>>Product Name:
>>Data Source:
>>Provider String:
>>Location:
>>Catalog:
>>
>>Thanks,
>>
>>Oskar
>
> At the risk of asking a silly question, have you created and tested
> the DSN successfully on the MSSQL server itself, and is the DSN a
> system DSN? Assuming it works OK from Control Panel, then I would try
> adding the linked server using Query Analyzer:
> EXEC sp_addlinkedserver
> @.server = 'Unify',
> @.provider = 'MSDASQL',
> @.datasrc = 'CORE_OFFICE1'
> Simon

Yes, the odbc driver is installed on the sql server box. However, I had
it as a User DSN instead of a System DSN. I changed that and now I get
this error:
Error 7399, authentication failed. Data source rejected connection attempt.

I put the 'sa' user as the local account on the linked server security
tab and I put the unix user/password as the remote account.
I think this is getting closer, but it's still not there!

Thanks,

Oskar

ps. In the odbc driver configuration, I put the DSN name, the server IP
address, the remote user account/password. On the USER DSN tab, when I
open it again, I can see all the details I put - they stayed there.
But on the SYSTEM DSN, I do the same thing but when I open the driver
config. again after I've entered the details the only thing that is
visible is the DSN. Everything else is blank. Why is that?|||Simon Hayes wrote:

> pheonix1t <pheonix1tAThoustonDOTrrDOTcom@.com.com> wrote in message news:<vvVTc.3077$np.309@.newssvr22.news.prodigy.com>...
>>hello,
>>I'm trying to create a linked server from an SQL2000 to a Unify ELS
>>(very old, odbc is version 1) database on SCO unix.
>>The odbc driver is old but it works fine when used by applications for
>>creating reports.
>>What I want to do is make a linked server from the SCO box to the win2k
>>box. So, I'm using enterprise manager to create the linked server.
>>The DSN of the unify database is CORE_OFFICE1
>>
>>I'm getting 'Error 7399: OLE DB provider 'MSDASQL' reported an error.
>>Datasource name not found and no default driver specified.
>>
>>When I create the linked server I chose the OLE DB for ODBC drivers.
>>I put 'CORE_OFFICE1' for the Data Source name. I also setup the
>>security with the name of a unix account for login/password.
>>What else should I have?
>>
>>I'm new at this linked server stuff!
>>
>>Product Name:
>>Data Source:
>>Provider String:
>>Location:
>>Catalog:
>>
>>Thanks,
>>
>>Oskar
>
> At the risk of asking a silly question, have you created and tested
> the DSN successfully on the MSSQL server itself, and is the DSN a
> system DSN? Assuming it works OK from Control Panel, then I would try
> adding the linked server using Query Analyzer:
> EXEC sp_addlinkedserver
> @.server = 'Unify',
> @.provider = 'MSDASQL',
> @.datasrc = 'CORE_OFFICE1'
> Simon

Also, when I do the above, I get this:
Server: Message 15429, Level 16, State 1, Procedure sp_addlinkedserver,
line 72, '(null)' is an invalid product name|||pheonix1t (pheonix1tAThoustonDOTrrDOTcom@.com.com) writes:
> Simon Hayes wrote:
>> EXEC sp_addlinkedserver
>> @.server = 'Unify',
>> @.provider = 'MSDASQL',
>> @.datasrc = 'CORE_OFFICE1'
>>
>> Simon
> Also, when I do the above, I get this:
> Server: Message 15429, Level 16, State 1, Procedure sp_addlinkedserver,
> line 72, '(null)' is an invalid product name

It seems you need to specify the @.srvproduct parameter. However, I
believe that it is sufficient to pass an empty string.

As for the login problem, I decline. I'm not very good at setting
up linked-server logins myself.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp