Thread: Duplicates

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    3
    Rep Power
    0

    Duplicates


    Disclaimer - My knowledge of MSSQL is limited. So I apologize in advance if this is a 'dumb' question.

    Purpose of Query - Gathering information from a multi-table database from a program our company uses and depositing the results into our web database so we can display some of the information.

    Issue - Duplicate information. Part of the issue is that there are multiple instances of people in the database, so there are results duplicated that way... and the other duplicates come from the join. I need to combine the results so that I deposit just one result with the same Name or Code (ID). The rest of the information I would like to combine so that I don't lose any information that may be needed.

    Here is query I have:
    SELECT
    people.Code AS [athlete-id],
    people.Name AS [athlete-name],
    people.DOB AS [DOB],
    people.Division AS [agency-id],
    certifs.[Expiration date] AS [expiration-date],
    groups.Name AS [agency-name],
    address.Addresses AS [address],
    address.City AS [city],
    address.State AS [state],
    case when tags.Field = 'ABJD86GHKXXQWA9Q'
    then tags.Value end AS [restrictions],
    case when tags.Field = 'VH2C78N9A15S059T'
    then tags.Value end AS [comments]

    FROM people LEFT OUTER JOIN
    certifs ON people.Code = certifs.Owner LEFT OUTER JOIN
    groups ON people.Division = groups.Code LEFT OUTER JOIN
    address ON people.Code = address.Owner LEFT OUTER JOIN
    tags ON people.Code = tags.Owner
    ORDER BY [agency-name], [athlete-name]

    Let me know of any questions you may have. I appreciate the help.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    Originally Posted by Patch1897
    ...and the other duplicates come from the join.
    this is what's going to drive you nuts

    based on your column names, i would guess the cardinality of the relationships as follows...
    • a person can have multiple certificates
    • a person can have multiple addresses
    • a person can have multiple tags

    this being the case, when you join all these other tables at the same time, you'll get cross-join effects

    for example, if a person has 3 certifs, 2 addresses, and 4 tags, your joins will produce 24 result rows

    i'm afraid the best that you can do is completely disassemble your query, return only one row per person from the person table (and any other tables, like the groups table, for which a person has only one row), and then retrieve the one-to-many data in the other tables with a separate query for each
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    3
    Rep Power
    0
    i'm afraid the best that you can do is completely disassemble your query, return only one row per person from the person table (and any other tables, like the groups table, for which a person has only one row), and then retrieve the one-to-many data in the other tables with a separate query for each
    This is where my lack of sql knowledge kills me. Will I be able to make multiple select statements to gather all of the different data, and then combine it into one table in one statement?

    The reason I ask if it can be done in one statement is because this statement will be run in a cron job each morning to redeposit the queried information of the multiple tables into one table on the web database.

    I really appreciate the help.. this has been driving me crazy.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    Originally Posted by Patch1897
    ...to redeposit the queried information of the multiple tables into one table on the web database.
    in that case, perhaps you could share the design of that table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    3
    Rep Power
    0
    Originally Posted by r937
    in that case, perhaps you could share the design of that table?
    I really appreciate the help.

    The design of that table is exactly the output of the select that I have above.

    [athlete-id], [athlete-name], [DOB], [agency-id],
    [expiration-date], [agency-name], [address],
    [city], [state], [restrictions], [comments] Are the column heads.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    Originally Posted by Patch1897
    The design of that table is exactly the output of the select that I have above.
    and how exactly do you want multiple expiration dates stored? what about multiple addresses? multiple tags?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo