Showing posts with label unique. Show all posts
Showing posts with label unique. Show all posts

Monday, March 12, 2012

How to Use NOT IN

Hi,

Im having a view(View1).Its unique keys are nvcrLocationCode, nvcrAssetCode.

Theres another table (Table1) thats also contain same unique keys.

I just want to filter records from View1 which are not in Table1.

Hint:

View1

nvcrLocationCode nvcrAssetCode

H001 A0001

W001 A0001

Table1

nvcrLocationCode nvcrAssetCode

H001 A0001

It should out put

W001 A0001

How do I do this

You could use EXCEPT operator (in SQL SERVER 2005 only) or NOT EXISTS clause:

Code Snippet

create table #View1

(

nvcrLocationCode nvarchar(4),

nvcrAssetCode nvarchar(5)

)

go

create table #Table1

(

nvcrLocationCode nvarchar(4),

nvcrAssetCode nvarchar(5)

)

go

select * fro

insert into #View1 values('H001', 'A0001')

insert into #View1 values('W001', 'A0001')

insert into #Table1 values('H001', 'A0001')

select * from #view1

EXCEPT

select * from #Table1

select * from #view1 v where NOT EXISTS

(

select * from #table1 t

where t.nvcrLocationCode=v.nvcrLocationCode and

t.nvcrAssetCode=v.nvcrAssetCode

)

|||you can also try this..

SELECT View1.*
FROM View1 LEFT OUTER JOIN
Table1 ON View1.nvcrLocationCode = Table1.nvcrLocationCode
AND View1.nvcrAssetCode = Table1.nvcrAssetCode
WHERE Table1.nvcrLocationCode IS NULL
AND Table1.nvcrAssetCode IS NULL

How to use multle values for Where clause

Hi, I have a unique problem that I am currently unable to figure out. I need to populate a where clause in a SQL statement that has multiple values, however those values always change because they are in another table. The end result that I want to end up with is a list of subs that belong to all of the UCI's that were selected for a particular bid number.

I have the following tables

tblBid with two columns. Bid_ID, and Uci_ID . This table contains multlple rows with the same Bid_ID but the Uci_ID is never the same for the current Bid_id. For example. If I had a Bid_ID of 123, I might have mutliple records listing

bid_id Uci_id

123 1000

123 2000

123 1050

tblSubs_By_Uci that has two columns. Sub_ID, and Uci_ID . This talbe contains a list of Uci_id's that Subs belong to. So I will have only multiple Sub_id and mulitple UCI_ID's because a sub can belong to mulitple Uci_ID's.

Uci_ID Sub_ID

1000 456

1000 2345

2000 456

1050 2345

2000 2345

This is the statement I am using to return the Uci's from the Bid table with bid_id of 123. For example. when I run the following sql statement, it will list all of the UCI's for bid_id 123. SELECT Uci_ID from tblBid where Bid_ID = 123 . That produces a list of UCI's. Now I want to find each sub that belongs to each of the UCI's using that list.

SELECT Sub_ID from tblSubs_BY_UCI where Uci_ID = (SELECT Uci_ID from tblBid WHERE Bid_ID = 123) . I of course get an error from sql saying that I can not pass multiple values to the Where clause.

Can someone please help point me in the right direction. I have been searching on the net for days trying to figure this out. I am open to any suggestions.

This is how you have to do it

SELECT Sub_ID from tblSubs_BY_UCI where Uci_ID IN (SELECT Uci_ID from tblBid WHERE Bid_ID = 123)

|||Try this... SELECT Sub_ID from tblSubs_BY_UCI where Uci_ID in (SELECT Uci_ID from tblBid WHERE Bid_ID = 123)|||

There are 2 ways.

SELECT Sub_IDfrom tblSubs_BY_UCIwhere Uci_IDin (SELECT Uci_IDfrom tblBidWHERE Bid_ID = 123 )-- or ----SELECT tb.Uci_ID , ts.Sub_IDfrom tblSubs_BY_UCI ts , tblBid tbwhere ts.Uci_ID = tb.Uci_IDand tb.Bid_ID = 123
Hope this will help.|||

Thanks.

By Changing the = to IN, it worked perfectly.