Query 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

Query help

Author

Topic

georous
Starting Member

12 Posts

Posted - 2007-02-09 : 08:24:19

hi i have the following query

SELECT T.UT AS UT1, COUNT(T.DE) AS KWDS_NE
FROM
(
SELECT KWDS.UT AS UT , KWDS.DE AS DE
FROM KWDS
WHERE KWDS.UT IN
(SELECT CC.UT_REF FROM CC WHERE CC.UT_ITEM = '000177977300010')
AND KWDS.DE NOT IN
(SELECT K.DE FROM KWDS AS K WHERE K.UT = '000177977300010')
) AS T
GROUP BY T.UT
ORDER BY T.UT

now what i want to do is to get results for a number of group in which
000177977300010 resides . This group is calles CS with fields cs.UT whereas kwds.ut=cs.ut
This query returns all kwds.de that do not exist in the cs.ut=000177977300010
the resulting data should contain cs.ut , count(t.de)

Thank you. and for any additional info regarding the database please don't hesitate to ask

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-09 : 08:30:08

This?

SELECT		KWDS.UT AS UT1,
		COUNT(KWDS.DE) AS KWDS_NE
FROM		KWDS
INNER JOIN	CC ON CC.UT_REF = KWDS.UT AND CC.UT_ITEM = '000177977300010'
LEFT JOIN	KWDS AS K ON K.DE = KWDS.DE AND K.UT = '000177977300010'
WHERE		K.DE IS NULL
GROUP		KWDS.UT
ORDER BY	KWDS.UT

Peter Larsson
Helsingborg, Sweden

georous
Starting Member

12 Posts

Posted - 2007-02-09 : 08:43:15

No Peter.
My query works fine as-is.
But it only works given a specific cc.ut_item.
Literally the CC.UT_ITEM = '000177977300010')
i want cc.ut_item to get values from a set of cc.ut_item .
This set/group is my CS where cs.ut=cc.ut_item.
i want to get results cc.ut_item , count(t.de)
but this cc.ut_item resides inside the sub-query so i cannot
get the values outside of it.

for example
...
WHERE kwds.ut IN
(SELECT cc.ut_ref FROM cc , cs WHERE cc.ut_item=cs.ut)
......
how can the value of cc.ut_item get out of that sub-query??
Thanx.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-09 : 08:47:22

You can if you use the ANSI JOIN technique which I used when I rewrote your query.
With INNER JOIN, you have access to the joined full table and not only one value as you do with the IN style.

If you still do not get it, you migth provide some sample data and your expected output based on the provided sample data.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-09 : 09:05:29

I am interested if there is a speed difference between your original query and my rewritten suggestion.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources