CODEWITHSUNDEEP
sqldistinct
V_H24
V_H24

Reputation: 139

Using DISTINCT does not work in the sql query

I have a following relationship.Database Relationship

and this is my query:

 sql = "SELECT DISTINCT g.ExeName, d.FileRec, d.RefDes, d.Title as [Component Title],
       c.Title AS [Sheet Title], f.FileName, f.Title AS [Sim Schematic Title] FROM
       (((((((Projects AS a INNER JOIN ProjectFile AS b ON a.RecNo=b.ProjectRec) 
       INNER JOIN Files AS c ON b.FileRec=c.RecNo) 
       INNER JOIN Components AS d ON c.RecNo=d.FileRec) 
       INNER JOIN CompP2Sim AS e ON d.RecNo=e.CompRec) 
       INNER JOIN p2Sim AS f ON e.p2SimRec=f.RecNo) 
       INNER JOIN ExeTable g ON f.ExeRec=g.RecNo) 
       INNER JOIN EffecP2Sim AS h ON f.RecNo=h.p2SimRec)
       WHERE d.StandardRec = " + b.Tag + " and a.RecNo = " + cbModel.SelectedValue + "
       AND c.SystemRec = " + (int)systemMode + 
       " AND d.RecNo IN (SELECT DISTINCT CompRec from CompP2Sim) AND h.ModelRec = " + cbModel.SelectedValue + " order by d.refdes";

But with this query I still get duplicates. How i can make the P2Sim's RecNo to be DISTINCT.

This is the result:

enter image description here

It is duplicating the same result, I want to just get the result with no p2sim RecNo duplicated. In this result, the file rec is different but they both have the same P2Sim's RecNo. I tried to group the query but I can't. I get an error every time I try to run it. "RefDes column can't be fined." It doesn't matter where I want to put the () to group the Distinct I get that error.

Upvotes: 4

Views: 8034

Answers (1)

Marc B
Marc B

Reputation: 360742

select distinct applies to the ENTIRE row, not just a field. e.g.

SELECT DISTINCT foo, bar, baz

should be read more as

SELECT DISTINCT(foo,bar,baz)

and not:

SELECT DISTINCT(foo), NON-DISTINCT(bar), NON-DISTINCT(baz)

If you want a particular field to be unique within your query, then you should group on that field.

Upvotes: 12

Related Questions