Getting multiple distinct data in each column from different tables

Question

I'm trying to get all distinct columns(projectName, villageName, blockName, lotName from 5 different tables(tbl_project,tbl_village,tbl_block,tbl_lot).

When I try to use this code:

SELECT DISTINCT`projectName`, `villageName`, `blockName`, `lotName`
                  FROM `tbl_property`
                  LEFT JOIN `tbl_project` ON tbl_property.projectID = tbl_project.projectID
                  LEFT JOIN `tbl_village` ON tbl_property.villageID = tbl_village.villageID
                  LEFT JOIN `tbl_block`   ON tbl_property.blockID   = tbl_block.blockID
                  LEFT JOIN `tbl_lot`     ON tbl_property.lotID     = tbl_lot.lotID
                  LEFT JOIN `tbl_model`   ON tbl_property.model     = tbl_model.modelID
                  LEFT JOIN `tbl_street`  ON tbl_property.street    = tbl_street.streetID
                  WHERE tbl_property.propertyStatus =  1  
                  GROUP BY `projectName`, `villageName`, `blockName`, lotName
                  ORDER BY `projectName` ASC, `villageName` ASC, `blockName` ASC, `lotName` ASC

I get the distinct projectName and villageName but when I add blockName: SELECT DISTINCTprojectName,villageName,blockName... It doesn't go distinct. This goes same with lotName. But it shows results but there is no distinction at all.

tbl_project

**projectName**
Project 1
Project 1
Project 2
Project 3

tbl_village

**villageName**
Village 1
Village 2 
Village 2
Village 4

tbl_block

**blockName**
Block 1
Block 2
Block 6
Block 6

tbl_lot

**lotName**
Lot 1
Lot 2 
Lot 3

Expected Output:

Project Name---- Village Name----Block Name----Lot Name

Project 1 ---------- Village 1-----------Block 1-------------Lot 1

Project 2 ---------- Village 2-----------Block 2-------------Lot 2

Project 3 ---------- Village 4-----------Block 6-------------Lot 3

please add the sample data (or create SQLFIDDLE ) and desired output will be helpfull — Noman, Sep 20 '16 at 14:10

score 0 · Answer 1 · edited May 23 '17 at 12:19

0

If you are adding 'blockName' to select list without purposefully leaving 'lotName' in GROUP BY clause, then it could be your problem. You can group by items not in your select list which will then display duplicates.

tldr, select items must match group by items AND remove unneeded joins.

For MYSQL see Also

Find duplicate records in MySQL

edited May 23 '17 at 12:19

Community

1
1

answered Sep 20 '16 at 14:19

Khris Kramer

68
6

Then the issue is with your joins. Comment out the bottom two joins to see if it returns distinct values now. -- LEFT JOIN `tbl_model` ON tbl_property.model = tbl_model.modelID -- LEFT JOIN `tbl_street` ON tbl_property.street = tbl_street.streetID – Khris Kramer Sep 20 '16 at 14:29
Still doesn't not return distinct values – Dylan King Sep 20 '16 at 14:34
Does this return duplicates? SELECT DISTINCT`projectName`, `villageName`, `blockName`, `lotName` FROM `tbl_property` LEFT JOIN `tbl_project` ON tbl_property.projectID = tbl_project.projectID LEFT JOIN `tbl_village` ON tbl_property.villageID = tbl_village.villageID LEFT JOIN `tbl_block` ON tbl_property.blockID = tbl_block.blockID LEFT JOIN `tbl_lot` ON tbl_property.lotID = tbl_lot.lotID WHERE tbl_property.propertyStatus = 1 – Khris Kramer Sep 20 '16 at 14:41
Yes. Its still returning duplicates – Dylan King Sep 20 '16 at 14:45
Try using a row_Number over(Partition by..) IF you are using MsSQL or Oracle. I'll post a rough example below. – Khris Kramer Sep 20 '16 at 15:10
If MYSQL see http://stackoverflow.com/questions/854128/find-duplicate-records-in-mysql#854177 – Khris Kramer Sep 20 '16 at 15:16
Dylan, Have you found a solution yet? I may have misunderstood your question. I assumed Row Duplicates. Column duplicates may be another solution such as Select Distinct value1 from table t join on table2 t2 t.ID = t2.ID Where condition UNION Select Distinct value2 from table t join on table2 t2 t.ID = t2.ID Where condition – Khris Kramer Sep 21 '16 at 14:15

Getting multiple distinct data in each column from different tables

1 Answers1