duplicate results from query

[phillip9]

Hello,

I have the following query that returns some duplicates:

SELECT DISTINCTROW [2005].expiration_date, cr2.property_name_text,
cr2.project_manager_name_text, cr2.contract_number, cr2.MshdaRFP
FROM 2005 INNER JOIN cr2 ON [2005].property_id = cr2.property_id;

query results = 275 records
table:cr2 = 250 records
table:2005 = 248 records

There are some duplicates in each of the tables (cr2 & 2005) by design, this
is because of the work-flow of the office.

I have tried all 3 types of Join's and also drawing my join line in both
directions; between the tables. Either way, the joins return about 275
records.

If you need more information from me, please let me know.

thank you,

phil

 

[Gerald Stanley]

Instead of SELECT DISTINCTROW try SELECT DISTINCT to reduce the number of
rows returned by the query.

Hope This Helps
Gerald Stanley MCSD

 

[phillip9]

That did help. I'm getting closer to the total I would like to have.

After changing DISTINCTROW to just DISTINCT. I get a result of 261 records,
but I would like to have just the records in one of the tables, which should
be about 250 or 248 records.

Thanks for the help.

new query is:
SELECT DISTINCT [2005].expiration_date, cr2.property_name_text,
cr2.project_manager_name_text, cr2.contract_number, cr2.MshdaRFP
FROM 2005 INNER JOIN cr2 ON [2005].property_id = cr2.property_id;

Instead of SELECT DISTINCTROW try SELECT DISTINCT to reduce the number of
rows returned by the query.

Hope This Helps
Gerald Stanley MCSD

Hello,

I have the following query that returns some duplicates:

SELECT DISTINCTROW [2005].expiration_date, cr2.property_name_text,
cr2.project_manager_name_text, cr2.contract_number, cr2.MshdaRFP
FROM 2005 INNER JOIN cr2 ON [2005].property_id = cr2.property_id;

query results = 275 records
table:cr2 = 250 records
table:2005 = 248 records

There are some duplicates in each of the tables (cr2 & 2005) by design, this
is because of the work-flow of the office.

I have tried all 3 types of Join's and also drawing my join line in both
directions; between the tables. Either way, the joins return about 275
records.

If you need more information from me, please let me know.

thank you,

phil

 

[John Spencer (MVP)]

Looks to me as if you are going to have to use an aggregate query and the
aggregate functions to return just one value from one of the tables. So group
by all the fields that are unique and then use Max or Min or First on the fields
that are not unique. Perhaps something like

SELECT MAX([2005].expiration_date) as LastExpiration, cr2.property_name_text,
cr2.project_manager_name_text, cr2.contract_number, cr2.MshdaRFP
FROM 2005 INNER JOIN cr2 ON [2005].property_id = cr2.property_id
GROUP BY cr2.property_name_text,
cr2.project_manager_name_text, cr2.contract_number, cr2.MshdaRFP

Since I don't know your data and don't know what is causing you to get
duplicates I arbitrarily decided that you might have more than one expiration
date in the 2005 table.

That did help. I'm getting closer to the total I would like to have.

After changing DISTINCTROW to just DISTINCT. I get a result of 261 records,
but I would like to have just the records in one of the tables, which should
be about 250 or 248 records.

Thanks for the help.

new query is:
SELECT DISTINCT [2005].expiration_date, cr2.property_name_text,
cr2.project_manager_name_text, cr2.contract_number, cr2.MshdaRFP
FROM 2005 INNER JOIN cr2 ON [2005].property_id = cr2.property_id;

Instead of SELECT DISTINCTROW try SELECT DISTINCT to reduce the number of
rows returned by the query.

Hope This Helps
Gerald Stanley MCSD

Hello,

I have the following query that returns some duplicates:

SELECT DISTINCTROW [2005].expiration_date, cr2.property_name_text,
cr2.project_manager_name_text, cr2.contract_number, cr2.MshdaRFP
FROM 2005 INNER JOIN cr2 ON [2005].property_id = cr2.property_id;

query results = 275 records
table:cr2 = 250 records
table:2005 = 248 records

There are some duplicates in each of the tables (cr2 & 2005) by design, this
is because of the work-flow of the office.

I have tried all 3 types of Join's and also drawing my join line in both
directions; between the tables. Either way, the joins return about 275
records.

If you need more information from me, please let me know.

thank you,

phil