help me to find missing info from the table

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)

help me to find missing info from the table

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2010-07-20 : 18:19:24

Hi,

I have a situation,

Tables:

Iteminfo
-- Itemnum
-- Orgid

Orginfo
-- Orgid

Orginfo is a master table it is having 6 organizations. The requirement is each item in the iteminfo table should have 6 organizations which is should be in the orginfo table.

I have some of the items are missing some of the organizations in iteminfo table. I want to find out the missing organizations for each item.

Could anyone please suggest me to achive this requirement.

Thanks in advance
-- Krishna

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-20 : 18:20:54

Will they all have the same 6 organizations? Or can the 6 be different?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2010-07-20 : 18:22:21

Thanks for the reply.6 are different organizations

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-20 : 18:27:52

So the requirement is that they have any 6, but it has to be 6? Can it be more than 6?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2010-07-20 : 18:31:02

No it can not be more than 6. Because we have only 6 organizations.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-20 : 18:40:42

If you only have 6, then how can they be different for each item?

Sorry for asking so many questions, but the answers are needed to come up with a solution.

It may help to provide us sample data, so that we don't have to go back and forth with questions.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2010-07-20 : 18:47:20

Sure, that will give the clear picture. I am sorry about that.
Iteminfo

Itemnum orgid
1000 AFc
1000 ASc
1000 SBC

1003 AFC
1003 ASC
1003 SBC
1003 FFC
1003 GFC
1003 NSC

1004 AFC

1005 AFC
1005 ASC
1005 SBC
1005 FFC
1005 GFC

1006 SBC
1006 FFC
1006 GFC

ORGinfo

Orgid
AFC
ASC
SBC
FFC
GFC
NSC

In the above data, item 1003 is having 6 organization. but rest of the items are missing some organizations.

For example item 1000 ismissing ffc, gfc and NSC. I want to find the missing org for each item in that table. Hope i am clear now. Please let me know if i did not explain you clearly.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-20 : 18:51:17

Thanks, it looks clear now. I'm heading out the door though, so I'll get back to you tomorrow. Unless someone else beats me to it!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2010-07-20 : 18:54:16

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-20 : 21:55:45

[code]
select *
from (
select distinct Itemnum
from Iteminfo i
) a -- replace with Item table if you have one
cross join ORGinfo b
left join Iteminfo i on a.Itemnum = i.Itemnum
and b.Orgid = i.Orgid
where i.Itemnum is null

OR

select *
from (
select distinct Itemnum
from Iteminfo i
) a
cross join ORGinfo b
where not exists
(
select *
from Iteminfo x
where x.Itemnum = a.Itemnum
and x.Orgid = b.Orgid
)
[/code]

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

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2010-07-20 : 22:30:55

Khtan, both of your SQL worked fine. Awesome !!!. This trick didn't raise on my brain.

Thank you very much.

Thank you Tara.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources