Solved

sql server query

Posted on 2016-08-31
12
59 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:ScottPletcher
Comment Utility
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
Comment Utility
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
Comment Utility
forgot to add the second image here it is
eeb.JPG
0
 

Author Comment

by:PeterBaileyUk
Comment Utility
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
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:PeterBaileyUk
Comment Utility
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
Comment Utility
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
Comment Utility
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 32

Accepted Solution

by:
Stefan Hoffmann earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you ste5an
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

772 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now