0

In this situation I have two tables joined together

Tables are: dpr and set1

I have this code: 

SELECT distinct s.s1T, s.s1W, s.s1L, s.s1Reject, s.s1Quantity, s.s1Volume
FROM set1 s left join dpr d ON s.DPno = d.ProdNo
Where s.DPno = '20150205'
AND s.s1T = d.Thickness 
AND s.s1W = d.Width
AND s.s1L = d.Length;

this is the result of this query in my Cyrstal Report

example: I just want to display the Quantity and Volume in set1

Quantity    Volume
----------------------
  23        54
  0         0
  23        54
  0         0

In this case my record is duplicating.. How can i Resolve this?

I tried this code for suppress if duplicated "{set2.Quantity}) = previous({set2.Quantity})" but its not working!

I am expecting only this result:

Quantity    Volume
----------------------
  23        54
  0         0
sqluser
  • 5,502
  • 7
  • 36
  • 50
Adlaremse Emerson
  • 11
  • 2
  • First, move `s.s1T = d.Thickness and s.s1W = d.Width and s.s1L = d.Length` this to your join -- in your `where` criteria, it negates the `outer join`. Then, if you only want those 2 distinct values, then just `select` those columns (not the others)... – sgeddes Feb 12 '15 at 03:50
  • Thanks for the help sir, you mean i need to remove s.s1T = d.Thickness and s.s1W = d.Width and s.s1L = d.Length in where criteria? i intended to choose those values not only Quantity and Volume because im gonna use it later also in my crystal report. – Adlaremse Emerson Feb 12 '15 at 03:56
  • is this what i going to do? SELECT distinct s2.T, s2.W, s2.L, s2.Reject, s2.Quantity, s2.Volume FROM set2 s2 left join dpr d on s2.DPno = d.ProdNo and s2.T = d.Thickness and s2.W = d.Width and s2.L = d.Length Where s2.DPno = '20150205' still not working :( – Adlaremse Emerson Feb 12 '15 at 04:01
  • There's no point in using a left join and then filting on columns from the inner side. You tried removing duplicates with a suppression formula but you only checked one of the columns. The ultimate problem is that you're getting duplicates because there is some duplication in the data itself (although if the entire rows were true duplicates then DISTINCT would have already eliminated them.) – shawnt00 Feb 12 '15 at 04:08
  • so what am i going to do sir? i was trying to fix this for a couple of days now but still i feel hopeless :( what should i do? – Adlaremse Emerson Feb 12 '15 at 04:19
  • when i tried that query in mysql it displays the record correctly because in my database i only got two records but when i tried to print it in crystal report it displays 4 records like the image above.. that means my record is duplicating – Adlaremse Emerson Feb 12 '15 at 04:22

1 Answers1

0

In general you can use a CTE with ranking function

;WITH C AS(
    SELECT RANK() OVER (PARTITION BY Quantity, Volume ORDER BY Quantity, Volume) AS Rnk
           ,Quantity
           ,Volume
    FROM yourTable -- Put your joins here
)
SELECT Quantity, Volume FROM C WHERE Rnk = 1

Based on the duplicate values you have fields for PARTITION BY and ORDER BY are changing

More information on here

Community
  • 1
  • 1
sqluser
  • 5,502
  • 7
  • 36
  • 50
  • ok :D i will try it.. and tell you if its working sir :D thanks i hope it will work – Adlaremse Emerson Feb 12 '15 at 04:34
  • WITH C AS(
    SELECT RANK() OVER (PARTITION BY s2.Quantity, s2.Volume
    ORDER BY s2.Quantity, s2.Volume) AS Rnk
    ,s2.Quantity
    ,s2.Volume
    FROM set2 s2 left join dpr d on s2.DPno = d.ProdNo and s2.T =
    d.Thickness and s2.W = d.Width and s2.L = d.Length Where s2.DPno = '20150205' ) SELECT s2.Quantity, s2.Volume FROM C WHERE Rnk = 1     – Adlaremse Emerson Feb 12 '15 at 05:20
  • You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WITH C AS( SELECT RANK() OVER (PARTITION BY s2.Quantity, s2.Volume ORDER BY' at line 1 – Adlaremse Emerson Feb 12 '15 at 05:26
  • Did you put a ; before WITH at the begining – sqluser Feb 12 '15 at 05:28
  • nop i didn't but i tried it also now, but got same problem... wait where do i exactly put it? i mean do i need to make a procedure or function in mysql? – Adlaremse Emerson Feb 12 '15 at 05:32
  • Ooooh My bad I thought you are using SQL Server. MySQL does not support Ranking functions but instead you have some alternatives. Have look at this post: http://stackoverflow.com/questions/3333665/rank-function-in-mysql – sqluser Feb 12 '15 at 05:36
  • I don't have MySQL to test – sqluser Feb 12 '15 at 05:37