Solved

sql server query

Posted on 2016-08-31
12
73 Views
Last Modified: 2016-09-05
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

0
Comment
Question by:PeterBaileyUk
  • 9
12 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41778062
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*/
0
 

Author Comment

by:PeterBaileyUk
ID: 41778205
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
0
 

Author Comment

by:PeterBaileyUk
ID: 41778206
forgot to add the second image here it is
eeb.JPG
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:PeterBaileyUk
ID: 41778281
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

0
 

Author Comment

by:PeterBaileyUk
ID: 41778336
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?
0
 

Author Comment

by:PeterBaileyUk
ID: 41779454
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
0
 

Author Comment

by:PeterBaileyUk
ID: 41779521
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
0
 

Author Comment

by:PeterBaileyUk
ID: 41779626
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
0
 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 41784381
There is no such concept of a saved query in a file.

You have two options:

1) Create a view from this query.
2) Use a CTE:

WITH SavedQuery AS
    (
        YourQueriesSqlStatement
    )
    SELECT *
    FROM otherTables T
      INNER JOIN SavedQuery Q ON Q.columns = T.columns;

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 41784800
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

0
 

Author Closing Comment

by:PeterBaileyUk
ID: 41784801
Thank you ste5an
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question