Solved

Follow Up TO Grouping/Output Question

Posted on 2006-06-14
11
171 Views
Last Modified: 2013-12-24
I had previously asked a question about getting a certain type of output and display from a query - relating to Count and Grouping. I thought I could then perform a join - but I overestimated my underestanding.

The method I used for getting the query to work were sub selects (thanks to those who responded!).

Here is some data from the table.
The headings are:
LATRecordID     SessionRecordID     Skill     PreCheckRating     Type     PostCheckRating

LATRecordID is just the entry or row number
SessionRecordID ties one persons record together across several tables - and will be slected by a form input - that I can get to work just fine.

The data is:

6730     247     E127     3     0     3
6731     247     E131     2     0     3
6732     247     G001     3     0     4
6733     247     G012     2     1     3
6734     247     G015     3     2     3
6748     248     E127     3     2     4
6749     248     E131     3     2     3
6750     248     G001     3     2     3
6751     248     G012     2     1     4
6752     248     G015     2     1     3
6753     248     G016     1     1     4
6764     248     K011     3     2     3
6765     249     C310     1     1     2
6779     249     K008     3     2     4
6780     249     K009     2     1     4
6781     249     K010     3     2     4
6782     249     K011     3     2     4
6783     251     C310     2     1     3
6784     251     E127     3     2     3
6785     251     E131     2     1     3
6786     251     G001     3     2     3
6787     251     G012     3     2     3
6788     251     G015     3     2     3
6789     251     G016     3     2     3
6790     251     G019     3     2     3

The query is basically:
select skill,
     (select count(PreCheckRating) from EE_TestTable where PreCheckRating = 1 and skill = E1.skill) as [preCheck-1],
     (select count(PreCheckRating) from EE_TestTable where PreCheckRating = 2 and skill = E1.skill) as [preCheck-2],
     (select count(PreCheckRating) from EE_TestTable where PreCheckRating = 3 and skill = E1.skill) as [preCheck-3],
     (select count(Type) from EE_TestTable where Type = 1 and skill = E1.skill) as [Type-1],
     (select count(Type) from EE_TestTable where Type = 2 and skill = E1.skill) as [Type-2],
     (select count(PostCheckRating) from EE_TestTable where PostCheckRating = 1 and skill = E1.skill) as [PostCheck-1],
     (select count(PostCheckRating) from EE_TestTable where PostCheckRating = 2 and skill = E1.skill) as [PostCheck-2],
     (select count(PostCheckRating) from EE_TestTable where PostCheckRating = 3 and skill = E1.skill) as [PostCheck-3]
from TableName E1
group by skill
order by skill

In this same table I have an identifying field called SessionRecordID, which is used across the database to tie many bits of information together. THis ID in turn is tied to a session and that is the filter I want to use for my data.

What I have done on other templates is to join the query pulling the data to the "filter" buy using the following:

INNER JOIN
SessionRecord ON
sessionRecord.sessionRecordID = TABLENAME.SessionRecordID)
WHERE
sessionID = #Form.SessionID#


When I do this for this particular template I do get the correct records pulled out, but the counts are for all entries. IOW - it selects the set of skill correctly, and it counts correctly - but it counts for every skill in the table regardless of session, not just for the session/sessionRecord I need.

The origional question is at:
http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_21878303.html

Thank you,
tim
0
Comment
Question by:timponder
  • 6
  • 5
11 Comments
 
LVL 2

Expert Comment

by:pharmacychoice
ID: 16906911
Tim --

If I am understanding you right, you are performing this inner join on the main from statement in the query.  If this is correct, what if you change your inner hoin to this:

INNER JOIN
SessionRecord ON
sessionRecord.sessionRecordID = E1.SessionRecordID)
WHERE
sessionID = #Form.SessionID#
0
 

Author Comment

by:timponder
ID: 16907045
Well that is what I thought - but I am doing something wrong somewhere else then - as it does not work. Regardless of the form entry - I always get the right skills but the total counts for all sessions.
 This is what it looks like as a whole (using a set value for testing instead of the form variable):

select skill,
     (select count(PreCheckRating) from EE_TestTable where PreCheckRating = 1 and skill = E1.skill) as [preCheck-1],
     (select count(PreCheckRating) from EE_TestTable where PreCheckRating = 2 and skill = E1.skill) as [preCheck-2],
     (select count(PreCheckRating) from EE_TestTable where PreCheckRating = 3 and skill = E1.skill) as [preCheck-3],
     (select count(Type) from EE_TestTable where Type = 1 and skill = E1.skill) as [Type-1],
     (select count(Type) from EE_TestTable where Type = 2 and skill = E1.skill) as [Type-2],
     (select count(PostCheckRating) from EE_TestTable where PostCheckRating = 1 and skill = E1.skill) as [PostCheck-1],
     (select count(PostCheckRating) from EE_TestTable where PostCheckRating = 2 and skill = E1.skill) as [PostCheck-2],
     (select count(PostCheckRating) from EE_TestTable where PostCheckRating = 3 and skill = E1.skill) as [PostCheck-3]
from

(TableName E1
INNER JOIN
SessionRecord ON
sessionRecord.sessionRecordID = E1.SessionRecordID)
WHERE
sessionID = 55

group by skill
order by skill


tim
0
 
LVL 2

Accepted Solution

by:
pharmacychoice earned 250 total points
ID: 16907082
You are getting the total counts because the sub-selects are not restricted to the session id.  They are pulling totals.  You may need to try to add the inner join into the sub-select as well to limit that number.  The way that it stands now, you are limiting the skills to the particular ones for the session, but since the sub-selects are not changing, all totals are still pulled.
0
 

Author Comment

by:timponder
ID: 16907202
I thought of that right after I sent the message.
I will give that a shot, I am sure you are right about the problem - I am just getting more and more doubtful about my ability.
:)
tim
0
 
LVL 2

Expert Comment

by:pharmacychoice
ID: 16907226
Well, you also have to consider that the way that this data is being pulled is a bit of a hack due to the structure of the data.  In a perfect world, there would be a relational table that associates your skills to the rest of the data and then this would be a bit easier to pull.  Do not start doubting your skills because my hack is not the easiest to follow.  ;-)
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:timponder
ID: 16907569
A hack I appreciate - since the database is set, at least for now.

I will see what I can do here - just seems like each time I think oh I can do that, I can then pretty much guarentee I will have a problem. :)

tim
0
 
LVL 2

Expert Comment

by:pharmacychoice
ID: 16907618
Let me know if this solution works.
0
 
LVL 2

Expert Comment

by:pharmacychoice
ID: 16934918
Tim,

Did this solution work for you?
0
 

Author Comment

by:timponder
ID: 16935033
Hi,
I am back on this piece now and _think_ I have it working.

Here is what I have with an explination below. Othere than the fact the database does not let me (us) do this in an ideal way, can you see anything that looks like it will present problems or is incorrect. The sample data seems to be returning the correct values. I only included two selects for precheck and type to save space:
====
select skill,
(select count(PreCheckRating)
 from (EE_TestTable
Inner Join SessionRecord ON sessionrecord.sessionrecordID = EE_TestTable.SessionRecordID)
where
SessionID = 55 AND PreCheckRating = 2 and skill = E1.skill) as [preCheck-2],

(select count(PreCheckRating)from (EE_TestTable
Inner Join SessionRecord ON sessionrecord.sessionrecordID = EE_TestTable.SessionRecordID)
where SessionID = 55  AND PreCheckRating = 3 and skill = E1.skill) as [preCheck-3]
from
(TableName E1
INNER JOIN
SessionRecord ON
sessionRecord.sessionRecordID = E1.SessionRecordID)
WHERE
sessionID = 55

group by skill
order by skill
==

That all seemed to work and pull the right data. I had to leave the second join in (outside of the counts) to get it to limit the skills to that session as well. Wasnt able to figur out any "short cut" for that.

What do you think?
tim
0
 
LVL 2

Expert Comment

by:pharmacychoice
ID: 16935301
Tim,

This looks like it should work to me.  I think that the only thing that you were missing previously was the join for your sub-selects.
0
 

Author Comment

by:timponder
ID: 16935331
Thank you again for all of your help - by talking through it I _almost_ understand how and why it works. :)

tim
0

Featured Post

Network it in WD Red

There's an industry-leading WD Red drive for every compatible NAS system to help fulfill your data storage needs. With drives up to 8TB, WD Red offers a wide array of solutions for customers looking to build the biggest, best-performing NAS storage solution.  

Join & Write a Comment

Suggested Solutions

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video discusses moving either the default database or any database to a new volume.

707 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

17 Experts available now in Live!

Get 1:1 Help Now