[RESOLVED] redundant record

I have redundant record. Record below show twice. How to avoid it

Quote:

---

Selangor, panchang Bedena, 1/2003

---

Quote:

---

.Open "SELECT [Prestasi hasil].Negeri,[Prestasi hasil].[Lokasi Projek],[Prestasi hasil].Musim,pemilik.Pemilik1,pemilik.Pemilik2,[Prestasi hasil].Plot,[Prestasi hasil].[Hasil Kasar (kg)],[Prestasi hasil].[Purata Hasil Kasar (Tan/ha)],[Prestasi hasil].[Hasil Bersih (-17 %)],[Prestasi hasil].[Purata Hasil Bersih (Tan/ha)],[Prestasi hasil].Masalah FROM [Prestasi hasil],pemilik where [Prestasi hasil].Plot=pemilik.Plot"

---

Have you redundant records? You select a lot of fields, and you showed us only 3... If all other fields you selected in the query and NOT showed here are identical too (so fields in the two rows are completely equals) you can try:

.Open "SELECT DISTINCT [Prestasi hasil].Negeri,[Prestasi hasil].[Lokasi Projek],[Prestasi hasil].Musim,pemilik.Pemilik1,pemilik.Pemilik2,[Prestasi hasil].Plot,[Prestasi hasil].[Hasil Kasar (kg)],[Prestasi hasil].[Purata Hasil Kasar (Tan/ha)],[Prestasi hasil].[Hasil Bersih (-17 %)],[Prestasi hasil].[Purata Hasil Bersih (Tan/ha)],[Prestasi hasil].Masalah FROM [Prestasi hasil],pemilik where [Prestasi hasil].Plot=pemilik.Plot GROUP BY [Prestasi hasil].Negeri,[Prestasi hasil].[Lokasi Projek],[Prestasi hasil].Musim,pemilik.Pemilik1,pemilik.Pemilik2,[Prestasi hasil].Plot,[Prestasi hasil].[Hasil Kasar (kg)],[Prestasi hasil].[Purata Hasil Kasar (Tan/ha)],[Prestasi hasil].[Hasil Bersih (-17 %)],[Prestasi hasil].[Purata Hasil Bersih (Tan/ha)],[Prestasi hasil].Masalah "

Best regards,
Xmas.

Before doing your INSERT, run a SELECT to see if the record already exists.

Xmas79 - why did you suggest DISTINCT and GROUP BY in the same query?

They basically produce the same output - right?

Quote:

---

Originally Posted by Xmas79

Have you redundant records? You select a lot of fields, and you showed us only 3... If all other fields you selected in the query and NOT showed here are identical too (so fields in the two rows are completely equals) you can try:

---

While correct (w/si's caveat), if all fields in the records are identical, you need to fix your db design. There should never be identical records in a table. If you don't want duplicate values in a field or group of fields, consider making them into a primary key. This will prevent duplicate entries. Si's method will allow you to check first, and give the user an error message when entering data.
If the selected fields can contain duplicates (i.e. all fields in the record, including those you haven't selected, aren't duplicates), use the DISTINCT or GROUP BY clauses. One of the gurus here once posted that GROUP BY is better, but can't recall why.

In some database engines DISTINCT is performed after the "working resultset" is completely built.

GROUP BY only inserts rows inthe the "working resultset" if they do not already appear. And of course GROUP BY processes any aggregate functions at the same time.

With MS SQL 2000 this difference went away - DISTINCT now uses the more powerful GROUP BY logic.

I would imagine that not all DB engines do this the same way.

Thanks, szlamany, that makes a lot of sense.

How to used Distinct for this SQL ?

Code:

---

InfoRS3.Open "Select * from bancian order by Plot", conn, adOpenDynamic, adLockOptimistic

---

I have alot of field name so I used * to show all field name.

As Salvelinus already indicated - you should never have duplicate rows in a single table.

Are you saying when you run this query

Select * from bancian order by Plot

You see duplicate rows??

Can you post a couple of them here and explain why?

no, I not run the SQl..

I just want to know where I need to put the DISTINCT if the SQL command like this because the sample they give stated the field name in the SQL command

Select * from bancian order by Plot

I hate to tell this...

It's

Select Distinct * from bancian order by Plot

I don't like DISTINCT. I want to always know why it's being used - as it usually is never needed.

Although - I just used it myself in a STORED PROCEDURE I'm writing for a web-page ;)

Code:

---

Select        Distinct SC.ConfData
        ,BU.BldgName
        ,TM.TeamDescr
        ,RM.Team
        ,RM.Bldg
        ,RM.Yr
        ,@RemoteUser
        From Building_T BU
        Left Join Room_T RM on RM.Yr=@Yr and RM.Bldg=BU.Bldg
        Left Join Stufiles_T SC on SC.ConfItem='DistrictName'
        Left Join Team_T TM on TM.Yr=RM.Yr and TM.Bldg=RM.Bldg and TM.Team=RM.Team
        Where BU.Bldg=@Bldg
        Order by RM.Bldg,RM.Team

---

I did this because there are dozens and dozens of rooms for a building in the ROOM_T table. But all I want is the "distinct" list of TEAM fields from that table (there might be only 3 or 4 different teams in a school building).

So I understand that my JOIN to ROOM_T is "weak" - but it's intentional because of what my end goal is - to get the DISTINCT TEAMS from that ROOM table.

Quote:

---

Xmas79 - why did you suggest DISTINCT and GROUP BY in the same query?

They bsically produce the same output - right?

---

They produce the same output, but they can procude different execution plans, depending on the engine and the query itself (ok, not the query above). But sorry for putting both in the same query...:o....
And sorry for answering late too... VBForums mail just arrived a few minutes ago...

Best regards,
Xmas.

Thanks for the clarification...

Quote:

---

Originally Posted by Xmas79

...they can procude different execution plans, depending on the engine and the query itself...

---

I discussed the execution plan differences in post #6 here...

Yes, I know... Credits of course go to you ;)

Best regards,
Xmas.

Quote:

---

Originally Posted by szlamany

As Salvelinus already indicated - you should never have duplicate rows in a single table.

---

For that matter, you should never have rows duplicated between tables, i.e. the same values in two tables.
The only exception I can see is if one of the tables has fields that can be populated by calculated values which can change over time. This would probably be better dealt with by a query, but there may be times not. Not the SQL guru myself.