hellp with select 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

SQL Server Development (2000)

hellp with select query

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-11-06 : 06:46:49

i have 2 tables :
table 1 :
companyid,personalid,stocktype
table2
companyid,stocktype

i want to make a query with all the
companyid that exist in table2, and to check in which of the companies exist stocktype in table 1 but NOT in table2?
how can i d o this?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-06 : 06:49:18

Care to post some sample data and expectd output?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-06 : 06:50:54

select table2.* from table2 left join table1 on table1.companyid = table2.companyid and table2.stocktype = table1.stocktype
where table1.companyid is null

Peter Larsson
Helsingborg, Sweden

EDIT: Changed from Table2 to Table1 and back for the people who can't do that themself

select table1.* from table1 left join table2 on table2.companyid = table1.companyid and table2.stocktype = table1.stocktype
where table2.companyid is null

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-06 : 06:51:08

[code]
Select * From Table2 tbl1 Left outer join
Table2 tbl2 on tbl2.stocktype = tbl1.stocktype
where
tbl1.stocktype is null
[/code]

Chirag

http://chirikworld.blogspot.com/

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-11-06 : 07:04:34

Peso : i at start did as yourquery but if you wil ltry you will see that stocktype that dosent appear on table 2 but exist in table1 wont appear in the query results!!!! - and that what i look for!
chiragkhabaria : your quesy isnt good beacuse i for each comany in sepeart need to do the stocktype check!
a data sample :
table1 :
companyid personalid stocktype


555         123          15
555         365          99
555         952          44

table2 :
companyid  stocktype
555         15
555         44

in this case stocktype 99 which exist in table1 for companyid : 555
dosent exist in table2 and that i look for!

thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

mightypenny_ph
Yak Posting Veteran

54 Posts

Posted - 2006-11-06 : 07:06:19

SELECT D1.companyID, D1.PersonalID, D2.StockType
FROM table1 D1 INNER JOIN table2 D2
ON D1.companyID = D2.companyID
WHERE D1.StockType IS NULL

hope this helps.... I maybe wrong... Please try it out...

SlayerS_`BoxeR` + [ReD]NaDa

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-06 : 07:07:38

Somthing like this ?

Select * From Table2 tbl2 Left outer join
Table1 tbl1 on tbl2.stocktype = tbl1.stocktype AND tbl2.companyid = tbl1.companyid
where
tbl1.stocktype is null

Chirag

http://chirikworld.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-06 : 07:15:08

Besides my previous query (which only needs to change from table1 to table2) this also works.

select	t1.*
from	@table1 t1
where	not exists (select * from @table2 t2 where t2.companyid = t1.companyid and t2.stocktype = t1.stocktype)

Peter Larsson
Helsingborg, Sweden

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-11-06 : 07:47:54

chiragkhabaria : test it an u will see that u wont get the missing stocktype
Peso : your code almost ok except for 1 thing : i need that in table2 that at least 1 companyid for each company will exist before i compare,
so if some companyid dosent exist at all on table 2 so there will be no compare with table 1

thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-06 : 08:36:14

You mean like you have sample data like this?

table1 :
companyid personalid stocktype
555         123          15
555         365          99
555         952          44
556         887          44

table2 :
companyid  stocktype
555         15
555         44

And the only valid return value is

555         365          99

?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-06 : 08:41:14

If you put in a little effort yourself, you will see that an easy extra WHERE will do the trick...

select		t1.*
from		table1 t1
left join	table2 t2 on t2.companyid = t1.companyid and t2.stocktype = t1.stocktype
where		t2.companyid is null
		and t1.companyid in (select t.companyid from table2 t)

Peter Larsson
Helsingborg, Sweden

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-11-07 : 01:34:00

PESO :

quote:

And the only valid return value is
555 365 99
?

yep i just need the companyid that have the stocktype diffrence!
but if u will check your query it dosnet get the goal that i need beacuse it give you all the stocktype from table2 that dosent exist for each company in table while i try to get the oppoiste goal!
making an inner join from table2 to table1 with the example i gave u wont give u any result beacuse the 2 type in table 2 exist already in table1!

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 01:44:00

Tell me what is wrong with my last suggestion and provide some more sample data.
Also provide and explain the expected output base don the supplied sample data.

Peter Larsson
Helsingborg, Sweden

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-11-07 : 11:03:16

your last query is great:)
imeant about the one before our answers get little mixtared and maybe i hae answred beore i have seen your new answer beacusei h ave answred withought refreshing

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources