• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

Problem with Select Distinct Query

Hi all,

As below as attached is my query to retrieve data from my database.

There is same multiple shortagepn from my database but i just wanted them to appear as 1 on my display table. the thing now is sometimes there is no data found or even duplicated shortagepn appear on my table. is there something wrong with my query or is there anyway i can use other than using the distinct method.

Thanks
Dim qryselect As String = "Select distinct projects.shortagepn, projects.pono,projects.description,projects.uploadby, projects.duedate, projects.poqty,projects.actualbyrname_cus,projects.programtype,projects.shortqty,projects.assembly_mo,outstandingpo.supplier_name,projects.sides,projects.familyid,projects.mono, companyname.companyname FROM (loading.projects INNER JOIN loading.outstandingpo ON outstandingpo.item_number = projects.shortagepn) INNER JOIN loading.companyname ON projects.familyid = companyname.familyid WHERE projects.familyid='" & familyid & "' AND projects.assembly_mo='" & assemblyno & "' AND projects.mono='" & mono & "' AND projects.shortagepn <>''"

Open in new window

0
bosscat
Asked:
bosscat
1 Solution
 
jfmadorCommented:
The Select Distinct will show you distinct row. Since you have multiple column they will be distinct if you look to all column

If you want only a list of projects.shortagepn you should only specify this column in your query

Select distinct projects.shortagepn FROM (loading.projects INNER JOIN loading.outstandingpo ON outstandingpo.item_number = projects.shortagepn) INNER JOIN loading.companyname ON projects.familyid = companyname.familyid WHERE projects.familyid='" & familyid & "' AND projects.assembly_mo='" & assemblyno & "' AND projects.mono='" & mono & "' AND projects.shortagepn <>''"
 
or use grouping using MAX, MIN, SUM, AVG, etc in your select statement.

Like :
Select distinct projects.shortagepn, Max(projects.duedate) as MaxDueDate, sum(projects.poqty) as sumpoqty
FROM (loading.projects INNER JOIN loading.outstandingpo ON outstandingpo.item_number = projects.shortagepn) INNER JOIN loading.companyname ON projects.familyid = companyname.familyid WHERE projects.familyid='" & familyid & "' AND projects.assembly_mo='" & assemblyno & "' AND projects.mono='" & mono & "' AND projects.shortagepn <>''"
GROUP BY projects.shortagepn
 
0
 
bosscatAuthor Commented:
thanks jfmador for the help!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now