Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sql server query

Posted on 2016-08-31
12
Medium Priority
?
93 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 70

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 35

Accepted Solution

by:
ste5an earned 2000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

636 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