join question.

Hello, folks
Desc. two tables table A
CriteriaID
1
2
3
Table B
UID ResultID CriteriaID
1 1 1
2 1 2
3 1 3
4 2 1
Want to build a query which gonna show me group by ResultID only records
from table B where B.CriteriaID = {1,2,3} from table APlease don't multi-post.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Jonh Smith" <support@.rtsplus.com> wrote in message
news:OIz61p0RFHA.3076@.tk2msftngp13.phx.gbl...
> Hello, folks
> Desc. two tables table A
> CriteriaID
> 1
> 2
> 3
> Table B
> UID ResultID CriteriaID
> 1 1 1
> 2 1 2
> 3 1 3
> 4 2 1
> Want to build a query which gonna show me group by ResultID only records
> from table B where B.CriteriaID = {1,2,3} from table A
>
>|||What do mean by "group By ResultID" If you group by resultID, then you will
get only one record in the ouput for each distinct value of ResultID {1,2}
which means that in the outout SQL needs t obe told what yo put in the other
columns.
We can easily just output the ResultID...
Select ResultID From TableB
Where CriteriaID In (Select CriteriaID From TableA)
Group By ResultID
-- --
or we can Add a count of original records...
Select ResultID, COunt(*)
From TableB
Where CriteriaID In (Select CriteriaID From TableA)
Group By ResultID
-- --
or we can Add a Sum, or Avg of original UID Values...
Select ResultID, Sum(UID), Avg(UID)
From TableB
Where CriteriaID In (Select CriteriaID From TableA)
Group By ResultID
-- --
... But if you want to Groyp BY, you have to tell the query processor what
else you want besides the Group By COlumn...
"Jonh Smith" wrote:

> Hello, folks
> Desc. two tables table A
> CriteriaID
> 1
> 2
> 3
> Table B
> UID ResultID CriteriaID
> 1 1 1
> 2 1 2
> 3 1 3
> 4 2 1
> Want to build a query which gonna show me group by ResultID only records
> from table B where B.CriteriaID = {1,2,3} from table A
>
>
>|||Thank you for your sugestion ! next time i will ..
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%234gars0RFHA.164@.TK2MSFTNGP12.phx.gbl...
> Please don't multi-post.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
> "Jonh Smith" <support@.rtsplus.com> wrote in message
> news:OIz61p0RFHA.3076@.tk2msftngp13.phx.gbl...
>|||Thank you for your response
It is has to be B.CriteriaID = 1 and B.CriteriaID = 2 and B.CriteriaID =
3 where {1,2,3} from table A
It is now make sense ?
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:DE24A35A-9344-4F69-8E23-F3F838B8C0B6@.microsoft.com...
> What do mean by "group By ResultID" If you group by resultID, then you
> will
> get only one record in the ouput for each distinct value of ResultID {1,2}
> which means that in the outout SQL needs t obe told what yo put in the
> other
> columns.
> We can easily just output the ResultID...
> Select ResultID From TableB
> Where CriteriaID In (Select CriteriaID From TableA)
> Group By ResultID
> -- --
> or we can Add a count of original records...
> Select ResultID, COunt(*)
> From TableB
> Where CriteriaID In (Select CriteriaID From TableA)
> Group By ResultID
> -- --
> or we can Add a Sum, or Avg of original UID Values...
> Select ResultID, Sum(UID), Avg(UID)
> From TableB
> Where CriteriaID In (Select CriteriaID From TableA)
> Group By ResultID
> -- --
> ... But if you want to Groyp BY, you have to tell the query processor what
> else you want besides the Group By COlumn...
> "Jonh Smith" wrote:
>|||Sorry, No, it doesn;t... B.CriteriaID cannot be = 1, AND = 2, AND = 3, all
at the same time...
Do you mean OR instead of of AND ?
But even then, that doesn't answer my question...
Sorry If I'm off-track, but it seems you might have some difficulties with
English.. Do you understand my question about "Group By" ? If Not, get
someone at your site who is a bit more fluent in English to read it and
explain it to you...
"Jonh Smith" wrote:

> Thank you for your response
> It is has to be B.CriteriaID = 1 and B.CriteriaID = 2 and B.CriteriaID
=
> 3 where {1,2,3} from table A
> It is now make sense ?
>
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:DE24A35A-9344-4F69-8E23-F3F838B8C0B6@.microsoft.com...
>
>|||I mean 'AND', .. so this is the point of my problem.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:9182725D-9E48-48D0-9430-F2F0C3D66491@.microsoft.com...
> Sorry, No, it doesn;t... B.CriteriaID cannot be = 1, AND = 2, AND = 3,
> all
> at the same time...
> Do you mean OR instead of of AND ?
> But even then, that doesn't answer my question...
> Sorry If I'm off-track, but it seems you might have some difficulties with
> English.. Do you understand my question about "Group By" ? If Not, get
> someone at your site who is a bit more fluent in English to read it and
> explain it to you...
> "Jonh Smith" wrote:
>|||I guess he is talking about relational division.
Relational Division
http://www.dbazine.com/ofinterest/o...br />
division
Example:
select
b.ResultID
from
tableB as b
inner join
tableA as a
on b.CriteriaID = a.CriteriaID and (a.CriteriaID in (1, 2, 3))
group by
b.ResultID
having
count(distinct b.CriteriaID) = count(distinct a.CriteriaID)
and count(distinct a.CriteriaID) = 3;
AMB
"CBretana" wrote:
> Sorry, No, it doesn;t... B.CriteriaID cannot be = 1, AND = 2, AND = 3, al
l
> at the same time...
> Do you mean OR instead of of AND ?
> But even then, that doesn't answer my question...
> Sorry If I'm off-track, but it seems you might have some difficulties with
> English.. Do you understand my question about "Group By" ? If Not, get
> someone at your site who is a bit more fluent in English to read it and
> explain it to you...
> "Jonh Smith" wrote:
>|||Case closed, thank you Alejandro Mesa.
This is solution
SELECT B.ResultID FROM A
JOIN B ON B.CriteriaID = A.CriteriaID
GROUP BY B.ResultID
HAVING (COUNT(B.ResultID) = (SELECT COUNT(CriteriaID ) FROM
A))
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:6D45BAB4-B39E-41DF-836E-8E2F50AA596D@.microsoft.com...
>I guess he is talking about relational division.
> Relational Division
> http://www.dbazine.com/ofinterest/o... />
20division
> Example:
> select
> b.ResultID
> from
> tableB as b
> inner join
> tableA as a
> on b.CriteriaID = a.CriteriaID and (a.CriteriaID in (1, 2, 3))
> group by
> b.ResultID
> having
> count(distinct b.CriteriaID) = count(distinct a.CriteriaID)
> and count(distinct a.CriteriaID) = 3;
>
> AMB
>
> "CBretana" wrote:
>|||Alej,
Got it now... Didn't understand...
On Celko's article (yr Link), I was familiar with the problem, but did
not know it was called "Relational Division".
I have in past used the "Not Exists... Where Not Exists..." syntax for
this issue, but I was unfamiliar with the approach you used. Thanks...
In his article, Celko makes the point that the two approaches return
different results when the divisor is empty, The nested Not Exists return
ALL records, and the Having Count(*)... approach returns an empty set...
On a purely academic note, I kinda think the former, nested approach,
which returns all records, is more mathematically "accurate"... Using Joes
example, All the pilots have the skill to fly "every" plane in an empty
hanger ...