Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2015
    Posts
    1

    Unanswered: Query - multiple joins will not return any results

    I've got 4 tables:
    divisions (ID, Division)
    teams (ID, Team)
    scores(ID, RelationID, Score)
    relatives (ID, RelationID, FieldID, ValID)

    The relatives table is to 'link' divisions and teams by RelationID
    The FieldID defines the table (1=divisions; 2=teams)
    The ValID = the ID of the given table.
    So, my fk is a combination of FieldID & ValID

    The output should look something like this:
    Code:
      Southwest Division | Tomcats  | 17
      Southwest Division | Bears    | 12
      Northeast Division | Tomcats  | 20
      Northeast Division | Lions    | 8
      Northeast Division | Cheetahs | 13
      Southeast Division | Cheetahs | 19
      Southeast Division | Lions    | 12
      Southeast Division | Zebras   | 6
    Any given team can be in any division.

    My Sql is:
    Code:
    SELECT DISTINCT divisions.Division, teams.Team, AVG(scores.Score) AS Score 
    FROM (answers 
            INNER JOIN ((relatives INNER JOIN divisions ON relatives.FieldID = 1 AND relatives.ValID = divisions.ID) 
            INNER JOIN teams ON relatives.FieldID = 2 AND relatives.ValID = teams.ID) ON answers.RelationID = relatives.RelationID) 
    GROUP BY Division, Team
    ORDER BY Division, Team
    This sql returns no records. Even if I remove the answers, GROUP and ORDER BY I get no records.

    How can I make the tables return what I need? I'd redesign the whole thing if needed.
    Last edited by gvee; 02-18-15 at 06:04. Reason: [code] tags

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    There are a few problems here. One big one is that you are selecting scores from a table that you haven't joined to. You are attempting to join to the relatives table on different criteria, I think you need another representation of that table or some better design. Once you have those items worked out and you are still having issues, give us another try.
    Dave

Tags for this Thread

Posting Permissions

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