Select Help - 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

General SQL Server Forums

New to SQL Server Programming

Select Help

Author

Topic

georous
Starting Member

12 Posts

Posted - 2007-02-07 : 12:43:56

Hello,
I'm trying to implement an SQL statement but i think i need some help.

My schema is
Table CC with Fields CC.UT_ITEM , CC.UT_REF
Table KWDS with Fields KWDS.UT , KWDS.DE

Now.. I have articles (CC.UT_ITEM , CC.UT_REF , KWDS.UT)that have keywords (KWDS.DE). Each article references a group of others (UT_ITEM , UT_REF).

Now what i'm trying to do is to get the average number of keywords (KWDS.DE) that exist in a specific (CC.UT_ITEM) article and do not exist in this article's references (group of CC.UT_REF for that CC.UT_ITEM).

Thank you in advance.

georous
Starting Member

12 Posts

Posted - 2007-02-07 : 12:52:30

OBVIOUSLY the following sql statement won't work.

SELECT CC.UT_REF , KWDS.DE FROM KWDS , CC
WHERE CC.UT_REF=
(SELECT C1.UT_REF FROM CC AS C1 WHERE C1.UT_ITEM='000177977300010' )
AND KWDS.DE IN
(SELECT KWDS1.DE FROM KWDS AS KWDS1 WHERE KWDS1.UT='000177977300010')

Some help??

Tks
Starting Member

48 Posts

Posted - 2007-02-07 : 13:11:14

Avg(KWDS.DE) GROUP BY CC.UT_ITEM?

gives the average KWDS.DE for every CC.UT_ITEM

using join and outer join will hide/ show rows pulled in by anohter table.

Tks

else i don´t untherstand you

georous
Starting Member

12 Posts

Posted - 2007-02-07 : 13:22:29

Let me give an example
Table CC

001 , 002
001 , 003

Table KWDS

001,word1
001,word2
001,word3
002,word1
002,word4
002,word5
003,word2
003,word5
003,word6

i'm trying to get the referenced articles (002 and 003) and the words that are in 001 and do not exist per referenced article.

my result should be
002,word2
002,word3
003,word1
003,word3

or even better
002 , 2
003 , 2
where 2 in the second row is the number of rows in the example above.
Maybe the average thing was confusing.

Thank you once again for your interest. :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-07 : 13:26:25

SELECT CC.UT_ITEM, COUNT(*)
FROM CC INNER JOIN KWDS ON KWDS.UT = CC.UT_REF
GROUP BY CC.UT_ITEM

Peter Larsson
Helsingborg, Sweden

georous
Starting Member

12 Posts

Posted - 2007-02-07 : 13:43:59

Peter thanks for your interest but that is way out of what i'm looking to get...
Given a specific CC.UT_ITEM i want to find the KWDS.DE of it's CC.UT_REF(=KWDS.UT) that this CC.UT_ITEM(=KWDS.UT='001') does not have.

select kwds.ut
from kwds
where kwds.ut=(select cc.ut_ref from cc where cc.ut_item='001')

and for those kwds.ut
select kwds.ut , kwds.de
from kwds
where kwds.de not in (select kwds.de from kwds where kwds.ut='001')

Something like that but it is not working on my dbms :(

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources