problem with joins

[cliff]

Hi,
I have the following tables

tbl_subrule_tloto

sburuleid ruleid rulemin rulemax
957 11 0 2
958 11 0 3

tbl_rule_values_tloto

ruleid ruleval
11 03
11 33
11 21
957 40
957 41
957 21
957 49
957 29
957 03
958 33
958 45
958 21
958 29

tbl_rndbnum

rndval
11
23
33
Now I want to see values in tbl_rule_values are fulfills condtions in
tbl_subrules_tloto. I have the following query but it does not work properly

for ex : I want to check rndval in tbl_rndnum matches values for ruleid
957 of tbl_rule_values_tloto and subruleid 957 in tbl_subrules_tloto and
also ruleid 11 of tbl_subrules_tloto matches values for ruleid 11 of
tbl_rule_values_tloto

SELECT tbl_SubRules_tloto.subruleid, tbl_SubRules_tloto.Ruleid
tbl_SubRules_tloto.RuleMin, tbl_SubRules_tloto.RuleMax,
Count(tbl_RndNum.RndVal) AS rulecount
FROM (tbl_SubRules_tloto LEFT JOIN tbl_Rule_values_tloto ON
(tbl_SubRules_tloto.Ruleid = tbl_Rule_values_tloto.RuleId) AND
(tbl_SubRules_tloto.subruleid = tbl_Rule_values_tloto.RuleId)) LEFT JOIN
tbl_RndNum ON tbl_Rule_values_tloto.RuleVal = tbl_RndNum.RndVal
GROUP BY tbl_SubRules_tloto.subruleid, tbl_SubRules_tloto.Ruleid,
tbl_SubRules_tloto.RuleMin, tbl_SubRules_tloto.RuleMax
HAVING (((Count(tbl_RndNum.RndVal))<[RuleMin] Or
(Count(tbl_RndNum.RndVal))>[RuleMax]));



please help to slove

 

[Dale Fye]

The reason it is not working is that you will never have a case where:

tbl_rule_values_tloto.ruleid = tbl_subrule_tloto.ruleID
AND
tbl_rule_values_tloto.ruleid = tbl_subrule_tloto.sburuleid

Why have you changed the table structure of the table I gave you back at the
end of October? What is the purpose of the sbuRuleID.

If I'd known this was for a loto picking program, I'd have asked for 10% of
all proceeds. LOL



--
HTH
Dale

email address is invalid
Please reply to newsgroup only.