Extracting information problems

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)

Extracting information problems

Author

Topic

neojakey
Starting Member

4 Posts

Posted - 2010-04-22 : 11:42:34

Morning all, I have limited experience with SQL Server, and I have to ask for help as I am unsure how to proceed and I have my client breathing down my neck.

Let me explain. I have membership table that contains a reference to membership categories. A Member can have multiple categories. Table structure is as follows:

**** TABLE NAME = MasterList ****
RecordID INT Auto
FirstName varchar(80)
LastName varchar(80)
There are more than this but is irrelevant at for this problem. The assigned categories table is as follows

**** TABLE NAME = Member_Assigned_Categories ****
MemCatID Int Auto
RecordID int
CatID int

I have another table of category descriptions, that begins with the CatID.

I have some example data:
Table MasterList:
56 Steve Johnson
57 Vince Jeffreys
58 Matt Slone

Table Member_Assigned_Categories:
67 | 58 | 2
68 | 57 | 22
69 | 58 | 22
70 | 56 | 9
71 | 58 | 6

I need to extract all the records from the materlist table that are assigned to categories 2 and 7. How would run write the SQL statement to do this.

I tried this:
Code:

SELECT COUNT(*) AS MemberCount FROM MasterList INNER JOIN  Member_Assigned_Categories ON  MasterList.RecordID=Member_Assigned_Categories.RecordID WHERE  Member_Assigned_Categories.CatID = 6 AND  Member_Assigned_Categories.CatID = 22;

and this:
Code:

SELECT COUNT(*) AS MemberCount FROM MasterList INNER JOIN  Member_Assigned_Categories ON  MasterList.RecordID=Member_Assigned_Categories.RecordID WHERE  Member_Assigned_Categories.CatID IN(6, 22);

But they never worked. I think I need to group the results or have a nested SQL statement. Please help, I am unsure how to resolve this.
Many thanks for your help with this matter

Paul
neojakey@gmail.com

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-22 : 12:01:58

You mean extract record where record id is assigned to both 2 as well as 7
or
either 2 or 7.

neojakey
Starting Member

4 Posts

Posted - 2010-04-22 : 14:16:37

When it is both 2 and 7..

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-22 : 15:03:27

[code]declare @tab1 table(rid int,
firstname varchar(80),
lastname varchar(80)
)
;

declare @tab2 table(mcid int,
rid int,
cid int)
;

insert into @tab1
select 56, 'Steve','Johnson'
union all
select 57, 'Vince', 'Jeffrys'
union all
select 58, 'Matt', 'Slone';

insert into @tab2
select 67, 58, 2
union all
select 68, 57, 22
union all
select 69, 58, 22
union all
select 70, 56, 9
union all
select 71, 58, 6
;

select count(*) from @tab1 a
join @tab2 b
on a.rid = b.rid
join @tab2 c on
b.mcid <> c.mcid and b.rid = c.rid and
b.cid <> c.cid
where b.cid in (2,6) and c.cid in (2,6)
;
[/code]

quote:
Originally posted by neojakey

When it is both 2 and 7..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 02:15:49

[code]
SELECT COUNT(*)
FROM
(
SELECT RecordID FROM MasterList INNER JOIN Member_Assigned_Categories ON MasterList.RecordID=Member_Assigned_Categories.RecordID
WHERE Member_Assigned_Categories.CatID IN(6, 22)
GROUP BY RecordID
HAVING COUNT(MemCatID)=2
)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

deepakugale
Starting Member

33 Posts

Posted - 2010-04-23 : 03:46:03

You need to extract all the records,then why are you using count(*) in your select query ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 04:04:48

for extracting all records this will do


SELECT RecordID FROM MasterList INNER JOIN  Member_Assigned_Categories ON  MasterList.RecordID=Member_Assigned_Categories.RecordID 
WHERE  Member_Assigned_Categories.CatID IN(6, 22)
GROUP BY RecordID 
HAVING COUNT(MemCatID)=2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

neojakey
Starting Member

4 Posts

Posted - 2010-04-27 : 10:47:51

quote:

Originally posted by hanbingl

declare @tab1 table(rid int,
firstname varchar(80),
lastname varchar(80)
)
;

declare @tab2 table(mcid int,
rid int,
cid int)
;

insert into @tab1 
select 56, 'Steve','Johnson'
union all
select 57, 'Vince', 'Jeffrys'
union all
select 58, 'Matt', 'Slone';

insert into @tab2
select 67, 58, 2
union all
select 68, 57, 22
union all
select 69, 58, 22
union all
select 70, 56, 9
union all
select 71, 58, 6
;

select count(*) from @tab1 a 
join @tab2 b 
on a.rid = b.rid 
join @tab2 c on 
b.mcid <> c.mcid and b.rid = c.rid and 
b.cid <> c.cid 
where b.cid in (2,6) and c.cid in (2,6)
;

quote:
Originally posted by neojakey

When it is both 2 and 7..

hanbingl Many thanks for your reply.

The number of categories can vary depending on user input, this statement works if there is more than one category, but fails when there is only one. I there a way to correct this for only one category.

Many thanks for your assistance with this problem.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 10:52:19

so you mean either one of 2 or 7 or both?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

neojakey
Starting Member

4 Posts

Posted - 2010-04-27 : 14:26:16

It could be IN(2,7,54,26) or it could be IN(7), depends on the input of the user..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-28 : 05:49:11

but IN (7) is already covered in earlier condition isnt it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources