asked on

sql server query

I have created a basic query but dont know how to finish it off.

I have this output:
Word      Word      CountOfword      CountOfTagword
AUTO      NULL        1        0
AUTO      AUTO        9647        9647

I have a tagged word table which are words that have been tagged previously (word has a pk its just counted here) its telling me that the word 'Auto' is in the tagged table 9647 of them. its also saying that 1 word 'auto' is in the tblwords that has not yet been tagged. I would like the query to identify the word where the word is already a word in tblwordtags but the pk is not. so here it would give me the first row with a view to adding that row into the tagged table later.

The word 'auto' has been selected just as an example. I hope to run a query like this as the new client data comes in. some words dont need to be tagged so looking for the null as above is not enough its got to be null with the word already existing in the tbltagwords.

It is like this (the numbers are only examples) :
1, 1 = word and tagwords equal therefore nothing to do
2, 1 = more entries of already tagged word appeared in word table (this words pk needs adding to wordtags table)
2, 0 = word not tagged

SELECT TblWords.Word,TblWordTags.Word, Count(TblWords.ClientCodeWordPosition) AS CountOfword, Count(TblWordTags.ClientCodeWordPosition) AS CountOfTagword
FROM TblWords LEFT JOIN TblWordTags ON (TblWords.ClientCodeWordPosition = TblWordTags.ClientCodeWordPosition)
GROUP BY TblWords.Word, TblWordTags.Word
having TblWords.Word='auto'
order by TblWords.Word;

Open in new window

Scott Pletcher

SELECT TW.Word,TWT.Word,
Count(TW.ClientCodeWordPosition) AS CountOfword,
Count(TWT.ClientCodeWordPosition) AS CountOfTagword
FROM TblWords TW
LEFT JOIN TblWordTags TWT ON (TW.ClientCodeWordPosition = TWT.ClientCodeWordPosition)
GROUP BY TW.Word, TWT.Word
HAVING
Count(TW.ClientCodeWordPosition) > Count(TWT.ClientCodeWordPosition)
AND Count(TWT.ClientCodeWordPosition) > 0 /*not 100% sure if you want this condition or not*/

PeterBaileyUk

ASKER

its not quite right as some legitimately do not have a countofTagword, I changed the query with a where on the auto and its returned the row but CountofTagword is not zero as auto is a word in the tagwords table.

The two screenshots give the idea. when i used the end statement that you were not sure of it returned no rows when it should have returned at least one row that being the lone 'auto' word in word table that is not in tabletagwords.

I hope ive explained it right.

in the second image you see the word 'Multi' it returns 23 from tblword and 0 from countoftagwords, which is correct as the word isnt a chosen tagword, 'Auto' is a chosen tagword yet shows zero as well.
eeA.JPG

PeterBaileyUk

ASKER

forgot to add the second image here it is
eeb.JPG

PeterBaileyUk

ASKER

I think I need a query within a query here are the counts that matter:

select TWT.Word, count(twt.clientcodewordposition) AS CountOfword
	FROM TblWordTags  TWT
	GROUP BY TWT.Word

Open in new window

PeterBaileyUk

ASKER

thinking sensibly this gives me the first row of auto where the pk is null in tagwords table.

SELECT TblWords.Word,TblWordTags.Word, Count(TblWords.ClientCodeWordPosition) AS CountOfword, Count(TblWordTags.ClientCodeWordPosition) AS CountOfTagword
FROM TblWords LEFT JOIN TblWordTags ON (TblWords.ClientCodeWordPosition = TblWordTags.ClientCodeWordPosition)
where TblWordTags.word is null and TblWords.word='auto'
GROUP BY TblWords.Word, TblWordTags.Word

Open in new window

I need to now connect that to this query, if the actual string word 'auto' appears in the following then it can return the row.

select TWT.Word, count(twt.clientcodewordposition) AS CountOfword
	FROM TblWordTags  TWT
	GROUP BY TWT.Word

Open in new window

How do i put the query 2 in query 1?

PeterBaileyUk

ASKER

I am trying to help myself here is a query that I think is almost there. I would like the actual total of words in the mix.

With the subquery I am getting the counts of vehicle words not in tagwordtable and also count in the tagword table. if I can get the total of actual word for the word auto its 9648 in the main query I think i can then get the logic in the math to select the right vehicle word.

SELECT TW.Word, sub.Word, Count(TW.word) AS CountOfWordinWordTable, sub.CountOfTagwordinTagtable
FROM TblWords TW LEFT JOIN TblWordTags ON (TW.ClientCodeWordPosition = TblWordTags.ClientCodeWordPosition)


join ( select TWT.Word, count(TWT.word) AS CountOfword, Count(twt.Word) AS CountOfTagwordinTagtable
	FROM TblWordTags TWT
	GROUP BY TWT.Word ) sub
	on TW.word=sub.word
	where TblWordTags.word is null
	GROUP BY TW.Word, TblWordTags.Word, sub.CountOfTagwordinTagtable

	--get total count of word
	select w.word, count(w.word)
	from TblWords w
	where w.Word='auto'
	group by w.word

Open in new window

example.JPG

PeterBaileyUk

ASKER

I thought this would work but it doesnt:

SELECT TW.Word, sub.Word, Count(TW.word) AS CountOfWordinWordTable, sub.CountOfTagwordinTagtable
FROM TblWords TW LEFT JOIN TblWordTags ON (TW.ClientCodeWordPosition = TblWordTags.ClientCodeWordPosition) left join myothersqlquery on field '**** here

PeterBaileyUk

ASKER

I did the operation in access but sql server is not allowing me to left join to a query thats saved, any help would be appreciated. QryTotalWordCounts.sql is a saved query

use Dictionary

SELECT TW.Word, sub.Word, Count(TW.word) AS CountOfWordinWordTable, sub.CountOfTagwordinTagtable
FROM TblWords TW LEFT JOIN TblWordTags ON (TW.ClientCodeWordPosition = TblWordTags.ClientCodeWordPosition) LEFT JOIN QryTotalWordCounts ON TblWords.Word = QryTotalWordCounts.Word

join ( select TWT.Word, count(TWT.word) AS CountOfword, Count(twt.Word) AS CountOfTagwordinTagtable
      FROM TblWordTags TWT
      GROUP BY TWT.Word ) sub
      on TW.word=sub.word
      where TblWordTags.word is null
      GROUP BY TW.Word, TblWordTags.Word, sub.CountOfTagwordinTagtable, sub.Word

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PeterBaileyUk

ASKER

I took your advice and it worked perfectly

use Dictionary

	select sum(CountOfword) as TotalWordCount, word, Cat, sum(CountOfTagword) as TotalTagWordCount
from
(


select count(D.clientcodewordposition) AS CountOfword, D.word, count(TWT.clientcodewordposition) AS CountOfTagword, vc.VehCategory as Cat
	FROM TblWords as D LEFT JOIN TblWordTags AS TWT ON (D.ClientCodeWordPosition = TWT.ClientCodeWordPosition) LEFT JOIN [TblVehCat] as VC ON D.ClientCode = vc.ClientCode
	where D.word='Auto'
	GROUP BY D.Word, vc.VehCategory
)a

GROUP BY Word, Cat
ORDER BY Word;

Open in new window

PeterBaileyUk

ASKER

Thank you ste5an