Re: Help writing a query

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Brian (bcap_at_IHATESPAMclara.co.uk)
Date: 01/03/05 

Date: Mon, 3 Jan 2005 15:02:24 -0000

"Kevin Brown" <nospam@nospam.please> wrote in message
news:efGomHa8EHA.2088@TK2MSFTNGP10.phx.gbl...
> Brian wrote:
> > "Kevin Brown" <nospam@nospam.please> wrote in message
> > news:u57P86W8EHA.2012@TK2MSFTNGP15.phx.gbl...
> >> I want to write a query which involves 4 tables and outer joins, but
> >> I'm just not getting it right. I'll list 4 example tables and an
> >> example desired result. Any ideas?
> >>
> >> Scenarios (id)
> >> ----------
> >> S1
> >> S2
> >> S3
> >> S4
> >> S5
> >>
> >> Groups (id)
> >> --------
> >> G1
> >> G2
> >> G3
> >>
> >> Members (group_id, player_id)
> >> ----------
> >> G1, P1
> >> G1, P2
> >> G1, P3
> >> G2, P1
> >>
> >> PlayerHistory (player_id, scenario_id)
> >> --------------
> >> P1, S1
> >> P1, S2
> >> P1, S3
> >> P2, S1
> >> P3, S1
> >>
> >> Desired Results (group_id, scenario_id, count)
> >> ----------------
> >> G1 S1 Count=3 (because P1/P2/P3 played S1)
> >> G1 S2 Count=1 (because P1 played S2)
> >> G1 S3 Count=1 (because P1 played S3)
> >> G1 S4 Count=0
> >> G1 S5 Count=0
> >> G2 S1 Count=1 (because P1 played S1)
> >> G2 S2 Count=1 (because P1 played S2)
> >> G2 S3 Count=1 (because P1 played S3)
> >> G2 S4 Count=0
> >> G2 S5 Count=0
> >> G3 S1 Count=0
> >> G3 S2 Count=0
> >> G3 S3 Count=0
> >> G3 S4 Count=0
> >> G3 S5 Count=0
> >>
> >> I want the results to list every scenario for every group and give a
> >> count of how many members of that group played that scenario. In
> >> this example: 3 groups * 5 scenarios = 15 rows. Each row having a
> >> count of the players of that group who played that scenario.
> >>
> >>
> >>
> >> Thoughts?
> >>
> >>
> >
> > Possibly something along these lines. The join expression is
> > probably going to want some parentheses somewhere but, if you
> > understand what I'm suggesting, you should be able to put it together
> > in the query designer. The key to it is that it's a CROSS JOIN
> > between Groups and Scenarios (i.e. there would be NO join line in the
> > query designer):
> >
> > SELECT G.id, S.id, Count(H.player_id) FROM Groups G, Scenarios S LEFT
> > JOIN Members M ON G.group_id = M.group_id LEFT JOIN PlayerHistory H ON
> > M.player_id = H.player_id GROUP BY G.id, S.id
>
> I understand why you want the CROSS JOIN - to acheive a cartesian product
> between groups and scenarios yielding the 15 rows in my example above.
I've
> tried putting parens as I thought appropriate around the joins, but I keep
> ending up with syntax errors.
>
> Here is my current guess (yielding a syntax error):
> SELECT G.id, S.id, Count(H.player_id)
> FROM Groups G,
> (Scenarios S LEFT JOIN Members M ON G.id = M.group_id) LEFT JOIN
> PlayerHistory H ON M.player_id = H.player_id
> GROUP BY G.id, S.id
>
> Thoughts?
>

The join is too ambitious for Access. The thing to do is to create and save
the cross join as a separate query, and then use that in your main query
instead of the Groups and Scenarios tables. If you're determined to do it
in one query, then you'll need to put the cross join in a subquery.

Relevant Pages

  • Re: Help writing a query
    ... > in the query designer. ... > between Groups and Scenarios (i.e. there would be NO join line in the ... between groups and scenarios yielding the 15 rows in my example above. ... ending up with syntax errors. ...
    (microsoft.public.access.queries)
  • Re: SQL to Linq - Left, Right and Inner Joins
    ... select distinct c.* from customers c cross join orders o ... Specifying a query with different constructs and then relying on the optimizer to make the best of it isn't optimal and will likely fail in some situations. ... Well you can easily add where clauses to each effective side of a join in LINQ: ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: badboytim
    ... I got a few syntax errors, since I a trying to learn SQL, I will take a ... Your group by query then becomes: ... I also tried the 'counta' ...
    (microsoft.public.access.queries)
  • Re: query expression too complex
    ... OFCapUOM and ConvUOM fields respectively. ... let me also state that you could redesign the query to use outer join ... <MS ACCESS MVP> ... character limit and as far as I can tell I haven't made any syntax errors ...
    (microsoft.public.access.queries)
  • Re: Query training -- Complex queries
    ... Second, let's take a little example, you could run in you Query Analyzer: ... from authors inner join authorsx ... A cross join that does not have a WHERE clause produces the Cartesian ... The size of a Cartesian product ...
    (microsoft.public.sqlserver.server)