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.

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

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.

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.

and how exactly do you want multiple expiration dates stored? what about multiple addresses? multiple tags?