Query sql server 2005 problem - 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 2005 Forums

Transact-SQL (2005)

Query sql server 2005 problem

Author

Topic

pelolori
Starting Member

15 Posts

Posted - 2009-07-07 : 06:09:37

Hi
i've a problem:

i've two table and i want to see only the values that is not common at the two tables: For ex:

T0

A1 A2
H06007691 F15FWL
H06007692 F15FWL
H06007693 F15FWL
H06007700 F15FWL

T1

A1
H06007691 F15FWL
H06007692 F15FWL
H06007693 F15FWL

result that i want

A1 A2
H06007700 F15FWL
which query does i have to do? Thanks a lot

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-07 : 06:12:08

select t.a1,t.a2 from to t
left join t1 as k on k.a1 = t.a1
where k.a1 is null

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-07 : 06:12:59

select * from to where a1 not in (select a1 from t1)

select * from to t where not exists (select * from t1 where a1 = t.a1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-07 : 06:13:25

You might need FULL OUTER JOIN if you have records in T0 not in T1 and also in T1 but not in T0.

KH
[spoiler]Time is always against us[/spoiler]

pelolori
Starting Member

15 Posts

Posted - 2009-07-07 : 08:43:02

quote:
Originally posted by bklr

select t.a1,t.a2 from to t
left join t1 as k on k.a1 = t.a1
where k.a1 is null

Thanks but if the table is the same, how can i do it?

For example:

T0

A1 A2 A3

X1 0 13.12.08
X2 0 23.12.08
X2 1 1.01.09

i'll see only Items that are available ( a2=0) to a certain date ( for example 30.04.09). The system has to show only x1 and not x2, inasmuch x2 is go out at 30.04.09. Thanks a lot

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-07 : 08:48:22

quote:
but if the table is the same, how can i do it?

what do you mean ? How to get records that is not common of same table ?

quote:
I'll see only Items that are available ( a2=0) to a certain date ( for example 30.04.09). The system has to show only x1 and not x2, inasmuch x2 is go out at 30.04.09. Thanks a lot

Please explain in more details.

KH
[spoiler]Time is always against us[/spoiler]

pelolori
Starting Member

15 Posts

Posted - 2009-07-07 : 08:54:32

quote:
Originally posted by khtan

quote:
but if the table is the same, how can i do it?

what do you mean ? How to get records that is not common of same table ?

quote:
I'll see only Items that are available ( a2=0) to a certain date ( for example 30.04.09). The system has to show only x1 and not x2, inasmuch x2 is go out at 30.04.09. Thanks a lot

Please explain in more details.

KH
[spoiler]Time is always against us[/spoiler]

Hi,
this is a second case:

i've a table with three column and in each rows there are serial ( column = a1 and row = x2), articles in warehouse or not to a certain date A2 (0= in and 1=out), date in or out.
i want to see serial in to a certain date. is it possible?

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-07 : 09:11:23

[code]SELECT row - RANK() OVER (PARTITION BY A1 order by row) session, *
FROM (
SELECT ROW_NUMBER OVER(ORDER BY A1, A3)row, A1, A2, A3
FROM T0
)a
WHERE session = 0[/code]
Not sure correct or not....

AMITOFO+AMEN+YA ALLAH Hope the query works

pelolori
Starting Member

15 Posts

Posted - 2009-07-07 : 09:37:04

is there any simple code????

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources