Solved

Follow Up TO Grouping/Output Question

Posted on 2006-06-14
11
175 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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
 

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

Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

Question has a verified solution.

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

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

839 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