Results 1 to 2 of 2

Thread: pliiiiiz help!

  1. #1
    Join Date
    Jun 2003
    Posts
    9

    Unanswered: pliiiiiz help!

    hi everybody !

    these are my tables :

    Tools : (RefTool, #RefC, #RefT)

    Coco : (RefC)

    Types : (RefT)

    Now i'd like to create a query that calculates the following statement :
    "for each Coco AND for each Type, how many Tools have the combination of the two Ref (RefC and RefT)" ?

    here's an exampl eto make sure this is clear :

    Tools RefTool RefC RefT
    1 3 2
    2 1 2
    3 1 2
    4 2 1
    5 2 2
    6 3 2

    Coco RefC
    1
    2
    3
    4

    Types RefT
    1
    2

    The query should give the following result :

    Result RefC RefT Number
    1 1 0
    1 2 2
    2 1 1
    2 2 1
    3 1 0
    3 2 2
    4 1 0
    4 2 0

    if this ain't still clear enough contact me at danydanymen@hotmail.com

    thx a lot gor helping me
    C U

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Here's a quick and dirty way to get the result set you show....

    Create a query called "crossref" with this SQL

    SELECT coco.*, types.*
    FROM coco, types;

    Create a second query with this SQL

    SELECT crossref.refc, crossref.reft, Count(tools.reftool) AS Number
    FROM tools RIGHT JOIN crossref ON (tools.reft = crossref.reft) AND (tools.refc = crossref.refc)
    GROUP BY crossref.refc, crossref.reft;

    It could probably be done with just one query, if you nested the first one
    inside the second one....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •