Solved

sql server query

Posted on 2016-08-31
12
83 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 15 55
Parsing this XML works but the other one doesn't 9 30
Many to one in one row 2 40
SQL Server Express or Standard? 5 34
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

730 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