JOIN problem 1

[eyorre]

I am trying to join two tables in a bit of an odd way. My tables are:

tblSeg:
RankID | SegAbbrev | LowerLimit | UpperLimit
1 | A | 6 | 100
2 | B | 3 | 5
3 | C | 0 | 2

and

tblX
RecID | Perc | CountOfID | CountOfStar
1 | 50 | 2 | 1
2 | 33 | 9 | 3
3 | 20 | 5 | 1
4 | 0 | 7 | 0

I need to find which SegAbbrev (or RankID) is appropriate to which RecID. This is worked out depending upon tblX.CountOfID being between the values of tblSeg.LowerLimit and tblSeg.UpperLimit

For instance I should get the result:
RecID | RankID | SegAbbrev
1 | 3 | C
2 | 1 | A
3 | 2 | B
4 | 1 | A

That is not quite it though. If tblX.Perc is greater than another variable lets call it varPerc then SegAbbrev for that record should be AAA and RankID should be 0. So the final result should actually be:

for varPerc=40
RecID | RankID | SegAbbrev
1 | 0 | AAA
2 | 1 | A
3 | 2 | B
4 | 1 | A

A little different from the usual type of join!
I am really struggling with this so if anyone can offer any ideas...
%-)

 

[JamesLean]

Try this (untested):

SELECT x.recid,
  CASE WHEN x.perc > @varperc THEN 0 ELSE s.rankid END,
  CASE WHEN x.perc > @varperc THEN 'AAA' ELSE s.segabbrev END
FROM tblx x JOIN tblseg s ON x.countofid BETWEEN s.lowerlimit AND s.upperlimit
ORDER BY x.recid

--James

 

[Pattycake245]

How about this:

declare @varperc int
set @varperc=40

select recid, rankid, segabbrev
from
(select a.recid, a.perc,
(case when a.perc > @varperc then 0 else b.rankid end) rankid,
(case when a.perc > @varperc then 'AAA' else b.segabbrev end) segabbrev
from tblx a join tblseg b on a.countofid >= b.lowerlimit and a.countofid <= b.upperlimit
group by a.recid, a.perc, rankid, segabbrev) as results

Tim

 

[eyorre]

Thanks James and Tim,
I have used your coding and I now have the following:

SELECT
100.0*SUM(CAST(tbl1.Star AS tinyint))/Count(*) AS Perc,
tbl1.RecID,
Count(*) AS CountOfID, 
SUM(CAST(tbl1.Star AS tinyint)) AS CountOfStar,
tbl2.RankID, 
(CASE WHEN 100.0*SUM(CAST(tbl1.Star AS tinyint))/Count(*)>40 THEN 0 ELSE tbl2.RankID END) AS MyRank
FROM tblX tbl1
JOIN tblSeg tbl2
ON (SELECT Count(*) FROM tblX)
BETWEEN tbl2.LowerLimit AND tbl2.UpperLimit
GROUP BY tbl1.RecID, tbl2.RankID
ORDER BY tbl2.RankID

As you can see I did simplify things a little in my question because CountOfStar, CountOfID and Perc are actually alias field names but hopefully that will not make any difference. I have not been able to use these alias names in the join though so have had to repeat the formula that produces them instead. I have also taken out the SegAbbrev as I can get by with just using RankID from tblSeg.

It is nearly there except I only get MyRank=1 or 0 rather than it taking on the appropriate value of RankID for all of my results:

RecID | Perc | CountOfID | CountOfStar | MyRank
1 | 50 | 2 | 1 | 0
2 | 33 | 9 | 3 | 1
3 | 20 | 5 | 1 | 1 (should be 2)
4 | 0 | 7 | 0 | 1

Any ideas as to where I am going wrong?

Thanks

 

[Pattycake245]

Might it have something to do with the fact you are querying the rank_id field?

SELECT
100.0*SUM(CAST(tbl1.Star AS tinyint))/Count(*) AS Perc,
tbl1.RecID,
Count(*) AS CountOfID,
SUM(CAST(tbl1.Star AS tinyint)) AS CountOfStar,
tbl2.RankID,
(CASE WHEN 100.0*SUM(CAST(tbl1.Star AS tinyint))/Count(*)>40 THEN 0 ELSE tbl2.RankID END) AS MyRank
FROM tblX tbl1
JOIN tblSeg tbl2
ON (SELECT Count(*) FROM tblX)
BETWEEN tbl2.LowerLimit AND tbl2.UpperLimit
GROUP BY tbl1.RecID, tbl2.RankID
ORDER BY tbl2.RankID

Take this out and try it(untested):

select recID, Perc, CountOfID, CountOfStar, MyRank
from
(SELECT
100.0*SUM(CAST(tbl1.Star AS tinyint))/Count(*) AS Perc,
tbl1.RecID,
Count(*) AS CountOfID,
SUM(CAST(tbl1.Star AS tinyint)) AS CountOfStar,
(CASE WHEN 100.0*SUM(CAST(tbl1.Star AS tinyint))/Count(*)>40 THEN 0 ELSE tbl2.RankID END) AS MyRank
FROM tblX tbl1
JOIN tblSeg tbl2
ON (SELECT Count(*) FROM tblX)
BETWEEN tbl2.LowerLimit AND tbl2.UpperLimit
GROUP BY tbl1.RecID, tbl2.RankID) as results
ORDER BY RecID

Tim

 

[eyorre]

Thanks Tim. This is giving me 'Internal SQL error', I think this something to do with the number of subqueries.

I have done a bit of delving and it looks like the problem lies with this bit:

JOIN tblSeg tbl2
ON (SELECT Count(*) FROM tblX)
BETWEEN tbl2.LowerLimit AND tbl2.UpperLimit

it always just counts the total number of records in tblX and therefore is always coming out within the highest limits. For example I actually have 67 records in my tblX so unless Perc>40 (when it correctly shows MyRank=0) then it is always showing the MyRank=1 (as it fits between the lower and upper limits of tblSeg.RankID=1).

Hope this makes sense! It is so close now!

 

[eyorre]

Basically Count(*) within:

JOIN tblSeg tbl2
ON (SELECT Count(*) FROM tblX)
BETWEEN tbl2.LowerLimit AND tbl2.UpperLimit

needs to be the same value as CountOfID, but it won't let me just replace Count(*) with CountOfID.

 

[Pattycake245]

Can you post your raw data from tblX that you use to come up with these numbers so that I can properly write a query instead of guessing to some degree. You can see how aliased columns can change the query entirely.

Tim

 

[eyorre]

Really appreciate this Tim. Here is tblX. (ID and NameID are irrelevant to the problem):


ID NameID RecID Star
6 18 10 0
8 19 10 0
12 22 19 0
16 18 25 1
17 19 25 0
18 20 25 0
21 18 9 0
23 15 9 0
28 19 18 0
176 18 15 0
178 20 15 1
179 10 15 1
180 9 15 1
181 33 15 1
182 32 15 1
199 33 39 0
200 31 39 0
209 33 41 0
210 31 41 0
211 22 41 0
212 34 41 1
225 18 44 0
226 20 44 0
227 39 44 0
228 32 44 1
243 30 46 0
244 45 46 0
245 32 46 0
246 19 46 1
247 9 46 1
248 20 46 1
249 41 46 1
250 33 47 0
251 31 47 1
252 20 47 0
253 9 47 0
254 45 47 1
255 41 47 1
284 34 49 1
285 30 32 1
286 18 50 0
287 20 50 0
288 39 50 1
296 30 51 1
297 18 51 0
298 42 51 0
299 31 51 0
300 39 51 0
301 22 51 0
302 44 51 0
311 30 42 0
312 31 42 1
313 25 42 0
314 39 42 0
315 42 45 1
316 32 45 0
317 20 45 0
318 41 45 1
319 10 45 1
320 33 33 1
321 18 33 0
322 15 33 1
323 32 33 0
324 19 33 0
325 39 33 1

Thanks.

 

[Pattycake245]

I think I see what's going on here. Is the RecID present in the tblSeg table as well? If not you're going to have problems. The reason why you get a MyRank of 1 for RecID 3 is that you are joining the two tables based on where the recordcount of tblX is between the lower and upper limits on tblSeg. The only time that condition is met is for RecID 1, so that gets chaged to 0. None of the other records match so it plugs in the the first value for the rest which is 1. There is no real clear cut join here, the query will simply plug in any value for the MyRank field. Is this what you are looking for, or am I missing something? Anyway I redid the query a little based on the table info you gave for your original data in the first post and got this result:

RecID Perc CountOfID CountOfStar MyRank
1 50 2 1 0
2 33 9 3 1
3 20 5 1 1
4 0 7 0 1

Here is the code:

select RecID, Perc, CountOfID, CountOfStar,
(case when Perc>40 then 0 else RankID end) MyRank
from
(select a.RecID, 100*((a.CountOfStar*1.0)/(a.CountOfID*1.0)) Perc, a.CountOfID, a.CountOfStar, a.totcnt, c.RankID
from
(select RecID, count(*) as CountOfID, SUM(CAST(Star AS tinyint)) CountOfStar, (select count(*) from tblX) totcnt from tblX group by RecID) a
join tblseg c on a.totcnt between c.lowerlimit and c.upperlimit
group by a.RecID, a.CountOfID, a.CountOfStar, a.totcnt, c.RankID)as results

This would work if you were looking to assign a particular rank to a recid and if there is no match make them 0, but this is currently the opposite of that.

Hope this helps, Tim

 

[eyorre]

Thanks Tim. It is a tricky little blighter. You are correct in assuming that there is no RecID in tblSeg, and that tblX and tblSeg are just joined according to the count of each RecID being between LowerLimit and UpperLimit.
So for example,

There are six occurances of RecID=15 in tblX so looking at tblSeg, 6 lies between the LowerLimit of 6 and the UpperLimit of 100 so the corresponding RankID=1. However in this case Perc (the percentage of the entries of this RecID where Star=1) is >40 so for RecID=15 RankID=0.

There are two occurances of RecID=10 in tblX, 2 lies between the LowerLimit of 0 and the UpperLimit of 2 so for RecID=10 the corresponding RankID=3. In this case Perc is 0% which is < 40 so for RecID=10 its RankID=3.

I will have a go with your new coding to see if I manipulate it a bit. It must be possible somehow. I really appreciate your time in helping with this.

 

[Pattycake245]

Ahhh, so the figure used to test between upper and lower limits is the total count of each individual recid, not the total number of records. Try this code, this should do it. The way the query was before was confusing me, as it looked as thoug you were using the total count of records.

This produced the desired result:

select RecID, Perc, CountOfID, CountOfStar,
(case when Perc>40 then 0 else RankID end) MyRank
from
(select a.RecID, 100*((a.CountOfStar*1.0)/(a.CountOfID*1.0)) Perc, a.CountOfID, a.CountOfStar, a.totcnt, c.RankID
from
(select RecID, count(*) as CountOfID, SUM(CAST(Star AS tinyint)) CountOfStar, (select count(*) from tblX) totcnt from tblX group by RecID) a
join tblseg c on a.CountOfId between c.lowerlimit and c.upperlimit
group by a.RecID, a.CountOfID, a.CountOfStar, a.totcnt, c.RankID)as results

 

[eyorre]

Tim - you are a genius! This gives me just the results I need. Thanks ever so much.

 

[Pattycake245]

No problem. I was hoping you would come back and check the post.

Tim