no duplicates in query

[gogirl]

Hello, I'm trying to figure out how to make the following sql statement work without pulling up duplicates (One record per ProductName).

SELECT tblDSProduct.ID, tblDSProduct.ProductName, tblBaseline.Phase, tblBaseline.PhaseStart, tblBaseline.PhaseFinish, tblBaseline.PhaseNotes
FROM tblDSProduct INNER JOIN tblBaseline ON tblDSProduct.ID = tblBaseline.ProductID;

I've tried DISTINCT and GROUP BY but have not been able to figure it out. Can someone help me please?

Thanks,

gogirl

 

[Golom]

Which "One" record?

If there are multiple records associated with a particular product name and you want to see all those other fields then you need to decide which of those other fields to display. Your choices are MIN, MAX, FIRST, LAST, AVG.

For example

SELECT  MAX(P.ID) As MaxID
     ,  P.ProductName
     ,  MAX(B.Phase) As MaxPhase
     ,  MAX(B.PhaseStart As MaxPhaseStart
     ,  MAX(B.PhaseFinish) As MaxPhaseFinish
     ,  FIRST(B.PhaseNotes) As FirstPhaseNotes

FROM tblDSProduct P INNER JOIN tblBaseline B
     ON P.ID = B.ProductID

GROUP BY P.ProductName

Alternatively, you may have other criteria such as the first ID for a ProductName

SELECT  P.ID
     ,  P.ProductName
     ,  B.Phase
     ,  B.PhaseStart
     ,  B.PhaseFinish)
     ,  B.PhaseNotes

FROM tblDSProduct P INNER JOIN tblBaseline B
     ON P.ID = B.ProductID

WHERE P.ID = (Select MIN(ID) From tblDSProduct X
              Where X.ProductName = P.ProductName)

Assuming that "ID" is a unique field.

 

[lespaul]

what results do you get now? In order for DISTINCT to return a single record EVERY field in your SELECT statement must have the same values for that ProductName.

You apparently have mutliple records in tblBaseline for each product (or is it the other way around?) Which of those multiple records do you want? The one with the latest date? Or is there some other identifier?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[gogirl]

Hi Golom, I'm getting a syntax error on the group by productname. Not sure what's causing it.

gogirl

 

[gogirl]

Hi lespaul, you are correct...there are multiple records in tblBaseline for each prudct. I want one record for each product. Eventually I will need to do a calculation on the dates but for now I'm just trying to get the query to work. Either using MAX or LAST on the dates will work for now until I figure the calculation out.

gogirl

 

[Golom]

Looks like I missed a closing paren

SELECT  MAX(P.ID) As MaxID
     ,  P.ProductName
     ,  MAX(B.Phase) As MaxPhase
     ,  MAX(B.PhaseStart[red][b])[/b][/red] As MaxPhaseStart
     ,  MAX(B.PhaseFinish) As MaxPhaseFinish
     ,  FIRST(B.PhaseNotes) As FirstPhaseNotes

FROM tblDSProduct P INNER JOIN tblBaseline B
     ON P.ID = B.ProductID

GROUP BY P.ProductName

 

[gogirl]

Thanks Golom, I didn't see that even after looking at it a few times. Anyway, that solved my problem. Thank you very much for your help!

gogirl