help 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 2005 Forums

Transact-SQL (2005)

help with select query

Author

Topic

bmango
Starting Member

12 Posts

Posted - 2009-03-06 : 10:58:58

hi there

I need some help with a select query and would be grateful for any help. Basically I want to list is those documents (held in one table) that belongs to one or more specific categories. The categories each document has are held in a joining table between the documents table and the categories table.

when I first tried to do this query I realised the results would not show individual documents that had one or more of these selected categories but would just show any documents that are linked to one or more of these selected categories. Let's say three categories were selected my initial query would not show a list of documents where each document had those three categories, but just those documents which linked to any of those three categories.

My initial SQL statement was:

SELECT * FROM lpp_docs_vw WHERE docid IN (SELECT docid_int FROM lpp_doccats WHERE catid_int = (35 OR 29))

Can anyone help with with the SQL statement I need please.

Thanks

Ben

raky
Aged Yak Warrior

767 Posts

Posted - 2009-03-06 : 11:07:45

Please post table structures, sample data and expected output

May be this

SELECT ldv.*
FROM lpp_docs_vw ldv
INNER JOIN lpp_doccats dc ON dc.docid_int = ldv.docid
AND dc.catid_int IN (35,29)

bmango
Starting Member

12 Posts

Posted - 2009-03-06 : 11:59:47

Hi raky,

Thanks for your response. I don't think that quite works. I think I can get the data I want from one table: lpp_doccats. This has 3 columns, an auto row id column, the document id and the category id (doccatid_au, docid_int and catid_int).

Lets say some sample data from the table is:

1 27 36
2 27 30
3 27 20
9 35 35
10 35 30
61 37 35
62 37 29
63 37 27
10 35 30

If i want to find those documents (second column) that have categories 30 and 36 (second column), then my query should only return document 27, as this is the only doc to have both those categories.

Ben

bmango
Starting Member

12 Posts

Posted - 2009-03-06 : 12:16:31

sorry in my reply above the catid_int column is the third column not the second.

Basically I want to use an AND clause on the same column. So catid_int = 36 AND 30

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-06 : 12:24:20

select t.docid_int from lpp_doccats t join lpp_doccats t1 on t.docid_int = t1.docid_int and t.catid_int = 36 and t1.catid_int = 30

bmango
Starting Member

12 Posts

Posted - 2009-03-06 : 12:35:49

Hi vijay,

Thanks for your reply. That works fine. However, what if I want to search on 3 categories? eg 36, 30 and 20? I will eventually want to search on 5 categories.

It seems to be getting quite complex (I am building the query on the fly in ASP depending on values selected from 5 drop down lists).

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-06 : 13:17:16

If you know what those 5 values are ...you can use PIVOT to convert them to columns and easily select them based on each columns...

here is how its done..

declare @t table (identid int,docid int,testid int)
insert @t
select 1,27,36 union all
select 2,27,30 union all
select 3,28,30 union all
select 4,28,35

select docid from
(
select docid,[30],[36],[35]
from (select docid,testid from @t) ps
PIVOT
(
sum(testid) for testid in ([30],[36],[35])
) as pvt
) t1
where t1.[30] = 30 and t1.[36] = 36

bmango
Starting Member

12 Posts

Posted - 2009-03-06 : 13:23:37

Hi Vijay,

That looks like a good solution. Many thanks.

Oh - I just realised I am using SQL Server 2000. Is there an alternative to PIVOT?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-06 : 13:24:30

Welcome...and good luck

bmango
Starting Member

12 Posts

Posted - 2009-03-06 : 13:32:05

Hi Vijay,

Sorry - I just realised I'm using SQL Server 2000. Is there an alternative to PIVOT?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-06 : 13:59:33

Ok..then you need this

declare @t table (identid int,docid int,testid int)
insert @t
select 1,27,36 union all
select 2,27,30 union all
select 3,28,30 union all
select 4,28,35

select docid from
(
select docid,
max(case when testid = 30 then testid else 0 end) as [30],
max(case when testid = 36 then testid else 0 end) as [36],
max(case when testid = 35 then testid else 0 end) as [35]
from @t group by docid
) t1
where t1.[30] = 30 and t1.[36] = 36

bmango
Starting Member

12 Posts

Posted - 2009-03-06 : 14:12:39

Many thanks :)

bmango
Starting Member

12 Posts

Posted - 2009-03-09 : 06:44:58

Hi Vijay

I've had a look at your solution, but I don't fully understand it. Would you mind explaining how it works? Particularly the initial stage for the temporary table.

Thanks,

Ben

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-09 : 07:15:55

[code]SELECT docid_int
FROM lpp_doccats
GROUP BY docid_int
HAVING MAX(CASE WHEN catid_int = 29 THEN 1 ELSE 0 END) = 1
MAX(CASE WHEN catid_int = 35 THEN 1 ELSE 0 END) = 1[/code]

E 12°55'05.63"
N 56°04'39.26"

bmango
Starting Member

12 Posts

Posted - 2009-03-09 : 08:09:16

Hi Peso,

That's fab, thank you very much. I'll use that. Do you mind explaining to me how it works?

Ben

bmango
Starting Member

12 Posts

Posted - 2009-03-09 : 09:20:06

Hi guys,

I posted this in the 2000 forum and got a good solution as follows:

select docid from lpp_doccats
where catid in (1,2)--parametrize this if needed
group by docid
having count(distinct catid)>1

thanks for your help.

ben

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources