Simple query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Simple query

Author

Topic

shifis
Posting Yak Master

157 Posts

Posted - 2003-08-25 : 14:25:09

Hi!!
I want to do a query that do the next
This is an example of my two tables
T1 -> values (1,2,3,4,5)
T2-> values (2,2,3,4,4)

This is what I want the query to returns
Return 1 and 5
The values in T1 that are not in T2.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-25 : 14:27:24

Something like this:

SELECT * FROM T1 WHERE ColumnA NOT IN
(SELECT ColumnX FROM T2)

Owais

shifis
Posting Yak Master

157 Posts

Posted - 2003-08-25 : 14:40:46

Mmm if I have more that one colum to check.
I have one table that contains the item per company and location, and other that have all the movements of inventory

Table 1 ITEMLOC ( Item per Location)
Table 2 ICTRANS (transaccion per item)
Join fiels:
company, location and item.

I need to returns all the items that did not have any movement until today ( we start using this new system two weeks ago).

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-08-25 : 14:42:42

Come on, Owais ... remember this?


select
    x
from
    t1
where
    not exists (
        select 1
        from
            t2
        where
            t1.x = t2.x)

Jay White
{0}

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-26 : 04:22:37

bad doby...bad, bad doby!!

You can modify Jay's query to exclude records by more than one column, something like this:

SELECT ItemID FROM ItemLoc i
WHERE NOT EXISTS
(
SELECT 1 FROM Transactions t
WHERE i.ItemID = t.ItemID
AND i.LocationID = t.LocationID
)

Owais

Make it idiot proof and someone will make a better idiot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources