Help with select statement

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblScan_tblAsset]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblScan] DROP CONSTRAINT FK_tblScan_tblAsset
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblAsset]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[tblAsset]
GO
CREATE TABLE [dbo].[tblAsset] (
PK[AssetID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT
NULL ,
[AssetName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[AssetTypeID] [int] NULL ,
[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DatelastScanned] [smalldatetime] NULL ,
[NextScanDate] [smalldatetime] NULL ,
[DateCreated] [smalldatetime] NULL ,
[LastModified] [smalldatetime] NULL ,
[Deleted] [bit] NULL
) ON [PRIMARY]
GO
----
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblEmployee]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblEmployee]
GO
CREATE TABLE [dbo].[tblEmployee] (
PK[EmpID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[EmployeeID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[DateCreated] [datetime] NULL ,
[LastModified] [datetime] NULL ,
[Deleted] [bit] NULL
) ON [PRIMARY]
GO
---
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblEmployeeAsset]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblEmployeeAsset]
GO
CREATE TABLE [dbo].[tblEmployeeAsset] (
PK[EmpID] [int] NOT NULL ,
PK[AssetID] [int] NOT NULL ,
[DateCreated] [datetime] NULL ,
[LastModified] [datetime] NULL ,
[Deleted] [bit] NULL
) ON [PRIMARY]
GO
---
tblAsset
AssetID AssetName
1 RyanPC
--
tblEMployee
EmpID EmployeeID
1 Ryan
--
tblemployeeasset
EmpID AssetID
1 1
I want a select statement that gives me this result...
Employee Asset
Ryan RyanPC"rhaazy" <rhaazy@.gmail.com> wrote in message
news:1151698511.458117.256010@.d30g2000cwa.googlegroups.com...
> I want a select statement that gives me this result...
> Employee Asset
> Ryan RyanPC
>
SELECT E.EmployeeID, A.AssetName
FROM tblAsset AS A
JOIN tblEmployeeAsset AS T
ON A.AssetID = T.AssetID
JOIN tblEmployee AS E
ON T.EmpID = E.EmpID ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||This works exactly the way I want in the querly analyzer thank you.
However, I am calling this select statement from within some C# code
via a dataadapter, when I run the query I get an error, any idea why?
SqlDataAdapter da = new SqlDataAdapter("select e.EmployeeID,e.EmpID,
a.AssetName, a.AssetId from tblAsset a join tblEmployeeAsset t on
a.assetID = t.AssetID join tblEmployee e on t.EmpID = E.EmpID", str);
DataSet ds = new DataSet();
da.Fill(ds);
dataGrid1.DataSource = ds;
The connection exists, and every other thing works, but why not this?
If this is beyond your scope i will promptly post in a C# forum...
David Portas wrote:
> "rhaazy" <rhaazy@.gmail.com> wrote in message
> news:1151698511.458117.256010@.d30g2000cwa.googlegroups.com...
>
> SELECT E.EmployeeID, A.AssetName
> FROM tblAsset AS A
> JOIN tblEmployeeAsset AS T
> ON A.AssetID = T.AssetID
> JOIN tblEmployee AS E
> ON T.EmpID = E.EmpID ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --

Help with NOT EXISTS query

I am having trouble with what will surely be a simple query for you experts.

I have 2 tables with inventory data.
IMITMIDX contains the master item info
IMINVLOC contains location specific data such as quantity on hand at that
location.

These tables have 2 commons fields, ITEM_NO and LOC

I need to search the IMINVLOC table for any records where ITEM_NO and LOC do
not match that in the IMITMIDX table.

The following query give me zero records even though I can manually find
some records:

SELECT *
FROM IMINVLOC_SQL INNER JOIN
IMITMIDX_SQL ON IMITMIDX_SQL.item_no = IMINVLOC_SQL.item_no
where not exists (select loc from iminvloc_sql where IMITMIDX_SQL.loc =
IMINVLOC_SQL.loc)

Any ideas?
Thanks.Hi

It is better to post DDL ( CREATE TABLE statements etc...) and example data
( as Insert statements ) than a description of pseudo code.

Either

SELECT L.*
FROM IMINVLOC L
WHERE NOT EXISTS ( SELECT * FROM IMITMIDX M WHERE M.ITEM_NO = L.ITEM_NO
AND M.LOC = L.LOC )

OR

SELECT L.*
FROM IMINVLOC L LEFT JOIN IMITMIDX M ON M.ITEM_NO = L.ITEM_NO
AND M.LOC = L.LOC
WHERE M.ITEM_NO IS NULL AND M.LOC IS NULL

John

"RDRaider" <rdraider@.sbcglobal.net> wrote in message
news:AQXEc.7015$qG.6055@.newssvr27.news.prodigy.com ...
> I am having trouble with what will surely be a simple query for you
experts.
> I have 2 tables with inventory data.
> IMITMIDX contains the master item info
> IMINVLOC contains location specific data such as quantity on hand at that
> location.
> These tables have 2 commons fields, ITEM_NO and LOC
> I need to search the IMINVLOC table for any records where ITEM_NO and LOC
do
> not match that in the IMITMIDX table.
> The following query give me zero records even though I can manually find
> some records:
> SELECT *
> FROM IMINVLOC_SQL INNER JOIN
> IMITMIDX_SQL ON IMITMIDX_SQL.item_no = IMINVLOC_SQL.item_no
> where not exists (select loc from iminvloc_sql where IMITMIDX_SQL.loc =
> IMINVLOC_SQL.loc)
>
> Any ideas?
> Thanks.|||Thank you very much for your help. I'm getting closer, let me try to state
my problem more clearly.
Every record in IMITMIDX must have a matching record in IMINVLOC with the
same ITEM_NO and LOC. IMINVLOC can have multiple records for the same item
in IMITMIDX (each location has a record). The query you provided gives me
records with item_no and loc that don't match that in imitmidx.

Example data:
Table: IMITMIDX
Item_no Loc
BRONZE SD

Table: IMINVLOC
Item_no Loc
BRONZE GSN
BRONZE RMN
BRONZE NS
BRONZE SA
BRONZE SD
BRONZE VIS
BRONZE WSD
BRONZE RAW

Your query returns the following: (record with LOC = SD is not
returned)
BRONZE GSN
BRONZE RMN
BRONZE NS
BRONZE SA
BRONZE VIS
BRONZE WSD
BRONZE RAW

I need a query that will tell me when the IMINVLOC table does not contain
the same Item_no/Loc combination as the Imitmidx table.

Thanks again for the help.

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:_rYEc.1174$rR4.10041557@.news-text.cableinet.net...
> Hi
> It is better to post DDL ( CREATE TABLE statements etc...) and example
data
> ( as Insert statements ) than a description of pseudo code.
> Either
> SELECT L.*
> FROM IMINVLOC L
> WHERE NOT EXISTS ( SELECT * FROM IMITMIDX M WHERE M.ITEM_NO = L.ITEM_NO
> AND M.LOC = L.LOC )
> OR
> SELECT L.*
> FROM IMINVLOC L LEFT JOIN IMITMIDX M ON M.ITEM_NO = L.ITEM_NO
> AND M.LOC = L.LOC
> WHERE M.ITEM_NO IS NULL AND M.LOC IS NULL
> John
> "RDRaider" <rdraider@.sbcglobal.net> wrote in message
> news:AQXEc.7015$qG.6055@.newssvr27.news.prodigy.com ...
> > I am having trouble with what will surely be a simple query for you
> experts.
> > I have 2 tables with inventory data.
> > IMITMIDX contains the master item info
> > IMINVLOC contains location specific data such as quantity on hand at
that
> > location.
> > These tables have 2 commons fields, ITEM_NO and LOC
> > I need to search the IMINVLOC table for any records where ITEM_NO and
LOC
> do
> > not match that in the IMITMIDX table.
> > The following query give me zero records even though I can manually find
> > some records:
> > SELECT *
> > FROM IMINVLOC_SQL INNER JOIN
> > IMITMIDX_SQL ON IMITMIDX_SQL.item_no = IMINVLOC_SQL.item_no
> > where not exists (select loc from iminvloc_sql where IMITMIDX_SQL.loc =
> > IMINVLOC_SQL.loc)
> > Any ideas?
> > Thanks.|||Hi

Maybe this way around?

SELECT M.*
FROM IMITMIDX M
WHERE NOT EXISTS ( SELECT * FROM IMINVLOC L WHERE M.ITEM_NO = L.ITEM_NO
AND M.LOC = L.LOC )

John

"RDRaider" <rdraider@.sbcglobal.net> wrote in message
news:EnZEc.7044$Ul1.576@.newssvr27.news.prodigy.com ...
> Thank you very much for your help. I'm getting closer, let me try to
state
> my problem more clearly.
> Every record in IMITMIDX must have a matching record in IMINVLOC with the
> same ITEM_NO and LOC. IMINVLOC can have multiple records for the same
item
> in IMITMIDX (each location has a record). The query you provided gives me
> records with item_no and loc that don't match that in imitmidx.
> Example data:
> Table: IMITMIDX
> Item_no Loc
> BRONZE SD
> Table: IMINVLOC
> Item_no Loc
> BRONZE GSN
> BRONZE RMN
> BRONZE NS
> BRONZE SA
> BRONZE SD
> BRONZE VIS
> BRONZE WSD
> BRONZE RAW
>
> Your query returns the following: (record with LOC = SD is not
> returned)
> BRONZE GSN
> BRONZE RMN
> BRONZE NS
> BRONZE SA
> BRONZE VIS
> BRONZE WSD
> BRONZE RAW
>
> I need a query that will tell me when the IMINVLOC table does not contain
> the same Item_no/Loc combination as the Imitmidx table.
> Thanks again for the help.
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:_rYEc.1174$rR4.10041557@.news-text.cableinet.net...
> > Hi
> > It is better to post DDL ( CREATE TABLE statements etc...) and example
> data
> > ( as Insert statements ) than a description of pseudo code.
> > Either
> > SELECT L.*
> > FROM IMINVLOC L
> > WHERE NOT EXISTS ( SELECT * FROM IMITMIDX M WHERE M.ITEM_NO = L.ITEM_NO
> > AND M.LOC = L.LOC )
> > OR
> > SELECT L.*
> > FROM IMINVLOC L LEFT JOIN IMITMIDX M ON M.ITEM_NO = L.ITEM_NO
> > AND M.LOC = L.LOC
> > WHERE M.ITEM_NO IS NULL AND M.LOC IS NULL
> > John
> > "RDRaider" <rdraider@.sbcglobal.net> wrote in message
> > news:AQXEc.7015$qG.6055@.newssvr27.news.prodigy.com ...
> > > I am having trouble with what will surely be a simple query for you
> > experts.
> > > > I have 2 tables with inventory data.
> > > IMITMIDX contains the master item info
> > > IMINVLOC contains location specific data such as quantity on hand at
> that
> > > location.
> > > > These tables have 2 commons fields, ITEM_NO and LOC
> > > > I need to search the IMINVLOC table for any records where ITEM_NO and
> LOC
> > do
> > > not match that in the IMITMIDX table.
> > > > The following query give me zero records even though I can manually
find
> > > some records:
> > > > SELECT *
> > > FROM IMINVLOC_SQL INNER JOIN
> > > IMITMIDX_SQL ON IMITMIDX_SQL.item_no = IMINVLOC_SQL.item_no
> > > where not exists (select loc from iminvloc_sql where IMITMIDX_SQL.loc
=
> > > IMINVLOC_SQL.loc)
> > > > > Any ideas?
> > > Thanks.
> >|||Thank you, that works!

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:iwZEc.1256$Dv5.10834047@.news-text.cableinet.net...
> Hi
> Maybe this way around?
> SELECT M.*
> FROM IMITMIDX M
> WHERE NOT EXISTS ( SELECT * FROM IMINVLOC L WHERE M.ITEM_NO = L.ITEM_NO
> AND M.LOC = L.LOC )
> John
> "RDRaider" <rdraider@.sbcglobal.net> wrote in message
> news:EnZEc.7044$Ul1.576@.newssvr27.news.prodigy.com ...
> > Thank you very much for your help. I'm getting closer, let me try to
> state
> > my problem more clearly.
> > Every record in IMITMIDX must have a matching record in IMINVLOC with
the
> > same ITEM_NO and LOC. IMINVLOC can have multiple records for the same
> item
> > in IMITMIDX (each location has a record). The query you provided gives
me
> > records with item_no and loc that don't match that in imitmidx.
> > Example data:
> > Table: IMITMIDX
> > Item_no Loc
> > BRONZE SD
> > Table: IMINVLOC
> > Item_no Loc
> > BRONZE GSN
> > BRONZE RMN
> > BRONZE NS
> > BRONZE SA
> > BRONZE SD
> > BRONZE VIS
> > BRONZE WSD
> > BRONZE RAW
> > Your query returns the following: (record with LOC = SD is not
> > returned)
> > BRONZE GSN
> > BRONZE RMN
> > BRONZE NS
> > BRONZE SA
> > BRONZE VIS
> > BRONZE WSD
> > BRONZE RAW
> > I need a query that will tell me when the IMINVLOC table does not
contain
> > the same Item_no/Loc combination as the Imitmidx table.
> > Thanks again for the help.
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:_rYEc.1174$rR4.10041557@.news-text.cableinet.net...
> > > Hi
> > > > It is better to post DDL ( CREATE TABLE statements etc...) and example
> > data
> > > ( as Insert statements ) than a description of pseudo code.
> > > > Either
> > > > SELECT L.*
> > > FROM IMINVLOC L
> > > WHERE NOT EXISTS ( SELECT * FROM IMITMIDX M WHERE M.ITEM_NO =
L.ITEM_NO
> > > AND M.LOC = L.LOC )
> > > > OR
> > > > SELECT L.*
> > > FROM IMINVLOC L LEFT JOIN IMITMIDX M ON M.ITEM_NO = L.ITEM_NO
> > > AND M.LOC = L.LOC
> > > WHERE M.ITEM_NO IS NULL AND M.LOC IS NULL
> > > > John
> > > > "RDRaider" <rdraider@.sbcglobal.net> wrote in message
> > > news:AQXEc.7015$qG.6055@.newssvr27.news.prodigy.com ...
> > > > I am having trouble with what will surely be a simple query for you
> > > experts.
> > > > > > I have 2 tables with inventory data.
> > > > IMITMIDX contains the master item info
> > > > IMINVLOC contains location specific data such as quantity on hand at
> > that
> > > > location.
> > > > > > These tables have 2 commons fields, ITEM_NO and LOC
> > > > > > I need to search the IMINVLOC table for any records where ITEM_NO
and
> > LOC
> > > do
> > > > not match that in the IMITMIDX table.
> > > > > > The following query give me zero records even though I can manually
> find
> > > > some records:
> > > > > > SELECT *
> > > > FROM IMINVLOC_SQL INNER JOIN
> > > > IMITMIDX_SQL ON IMITMIDX_SQL.item_no = IMINVLOC_SQL.item_no
> > > > where not exists (select loc from iminvloc_sql where
IMITMIDX_SQL.loc
> =
> > > > IMINVLOC_SQL.loc)
> > > > > > > > Any ideas?
> > > > Thanks.
> > > > > > >|||> I have 2 tables with inventory data. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

>> IMITMIDX contains the master item info;
IMINVLOC contains location specific data such as quantity on hand at
that
location. These tables have 2 common fields [sic], ITEM_NO and LOC <<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. I would assume from this
narrative that IMITMIDX should not have a location at all, but only
information about the items -- UPC, size, weight, color, etc. and that
it would be referenced by the
IMINVLOC table for the quantity at each location (warehouses?,
stores?).

>> I need to search the IMINVLOC table for any records [sic] where
ITEM_NO and LOC do not match that in the IMITMIDX table. <<

>> The following query give me zero records [sic]though I can manually
find some records [sic] <<

Why did you put "_SQL" postfixes on the names in the query? Never use
SELECT * in production code; I have no choice because I have no DDL:

SELECT I1.*, L1.*
FROM Imitmidx AS I1
LERFT OUTER JOIN
IminvLoc AS L1
ON I1.item_no = L1.item_no
AND I1.loc = L1.loc;

This will give you NULLs for the unmatched rows.

Never use uppercase letters for names (it is unreadable; that is why
newspapers and books are mixed case). Get a copy of ISO-11179 and
starting using the standards for data element names, too.

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