Simplify my Query using the Join statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Simplify my Query using the Join statement

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-06-04 : 02:20:15

Hi Forumer's

I have a query that will check the records from other table.
I could not figure to incorporate all my requirements.
Need your help guys. Thanks.

[Code]
--Requirements
1. if the ESN record in Data1 have only operation_type 4 and 5, the remarks should be 'Cores'
2. if the ESNs found no records from the table the remarks should be 'No records'
3. if the ESN have the operation type 2 the remarks is 'Not yet returned'

---Data
Create table #Sample
(ESN nvarchar(35))
Insert #Sample (ESN) Values ('268435459404494586')
Insert #Sample (ESN) Values ('268435459404494781')
Insert #Sample (ESN) Values ('268435459404507597')

Create table #Data1
(ESN nvarchar(35), operation_type int, rma_type int)
Insert #Data1 (ESN,operation_type, rma_type) Values ('268435459404494586',4, 5)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404494586',5, 5)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404507597',1, NULL)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404507597',3, NULL)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404507597',52,NULL)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404507597',2, NULL)

--Query
Select s1.ESN,
d1.rma_type,
d1.operation_type,
Case When d1.operation_type=2 Then ' Not yet returned '
When d1.operation_type= Then ' No Records'
When d1.operation_type=4 OR d1.operation_type=5 Then ' Cores'
End As 'Remarks'
from #Sample s1
Left Outer Join #Data1 d1 --not yet returned
On d1.ESN = s1.ESN and d1.operation_type = 2
Left Outer Join #Data1 d2 -- no recounrds found
On d2.ESN = s1.ESN
Group by s1.ESN, d1.rma_type, d1.operation_type

--Result
ESN |Remarks
---------------------------------------
268435459404494586| Cores
268435459404494781| No records
268435459404507597| Not yet Returned

Drop table #Data1
Drop table #Sample[/Code]

Thank you in Advance.

JOV

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-04 : 04:46:25

You can avoid all those Joins by using one single Join as follows:


Select Distinct * From
(Select a.ESN,
(Case When b.operation_type IN (4,5) Then 'Cores'
	  When a.ESN Not IN (Select ESN From #Data1) Then 'No Records'
	  When b.operation_type = 2 then 'Not Yet Returned'
	  Else ''
 End) As Remarks
From #Sample As a
Left JOIN #Data1 As b ON a.ESN = b.ESN) As c
Where Remarks <> ''

That might improve Performance of the query.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-06-04 : 05:09:41

Hi Vinu. Thank you for your reply.
I will add some scenario for the "Cores" Remakrs.
my Previous post that the cores it should be operation type 4 & 5 (no other transaction in ESN)
what if i have other operation type asie from 4 & 5 from the ESNs.
Please take a look my sample data.

Btw, i place additional condition rma_type=5 and operation_type in (4,5)
but the result i got i have duplication ESNs with different remarks. it should be only one ESNs.
the correct result should be.

ESN|Remarks
------------------------------------
268435459404494586 Not Yet Returned
268435459404494781 No Records
268435459404507597 Not Yet Returned

[code]
--Data
Create table #Sample
(ESN nvarchar(35))
Insert #Sample (ESN) Values ('268435459404494586')
Insert #Sample (ESN) Values ('268435459404494781')
Insert #Sample (ESN) Values ('268435459404507597')

Drop table #Data1

Create table #Data1
(ESN nvarchar(35), operation_type int, rma_type int)
Insert #Data1 (ESN,operation_type, rma_type) Values ('268435459404494586',4, 5)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404494586',5, 5)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404494586',1, 5)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404494586',2, 5)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404494586',3, 5)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404507597',1, NULL)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404507597',3, NULL)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404507597',52,NULL)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404507597',2, NULL)

Select Distinct * From
(Select a.ESN,
(Case When b.rma_type=5 and b.operation_type IN (4,5) Then 'Cores'
When a.ESN Not IN (Select ESN From #Data1) Then 'No Records'
When b.operation_type = 2 then 'Not Yet Returned'
Else ''
End) As Remarks
From #Sample As a
Left JOIN #Data1 As b ON a.ESN = b.ESN) As c
Where Remarks <> ''

mani_12345
Starting Member

35 Posts

Posted - 2012-06-04 : 05:15:48

try this ...

with cte_name as (

select distinct coalesce(s1.esn,d1.esn )"ESN",
case when d1 .operation_type = 4 OR d1 .operation_type =5 then 'cores'
when d1.operation_type =2 then 'not yet returned '
when d1.operation_type IS null then 'no record '
end "output"

from #Sample s1 full outer join #Data1 d1 on s1.ESN =d1.ESN
)

select * from cte_name where output is not null

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-06-04 : 05:27:35

Thank for your reply.

I make some adjustment in the script and add the rma_type =5
but the result i got duplicate ESN. it should be display one ESNs.

ith cte_name as (

select distinct coalesce(s1.esn,d1.esn )"ESN",
case when d1 .operation_type = 4 OR d1 .operation_type =5 and d1.rma_type=5 then 'cores'
when d1.operation_type =2 then 'not yet returned '
when d1.operation_type IS null then 'no record '
end "output"

from #Sample s1 full outer join #Data1 d1 on s1.ESN =d1.ESN
)

select * from cte_name where output is not null

ESN|Output
---------------------------
268435459404494586 cores
268435459404494586 not yet returned
268435459404494781 no record
268435459404507597 not yet returned me where output is not null

Correct result: since i have the operation type 4,5,1,2,3 for this ESN "268435459404494586"
the result will be "not yet returned" . if the operation type is 4,5 other than that the result will be "cores"
--------------
ESN|Output
----------------
268435459404494586 not yet returned
268435459404494781 no record
268435459404507597 not yet returned me where output is not null

mani_12345
Starting Member

35 Posts

Posted - 2012-06-04 : 05:36:58

i guess u r doing something wrong i changed it too and getting same ans

with cte_name as (

select distinct coalesce(s1.esn,d1.esn )"ESN",
case when d1 .operation_type = 4 OR d1 .operation_type =5
and d1.rma_type =5 then 'cores'
when d1.operation_type =2 then 'not yet returned '
when d1.operation_type IS null then 'no record '
end "output"

from #Sample s1 full outer join #Data1 d1 on s1.ESN =d1.ESN
)

select * from cte_name where output is not null

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-04 : 05:53:15

I have done nothing but simply put the following 3 business requirements into a query:

1. if the ESN record in Data1 have only operation_type 4 and 5, the remarks should be 'Cores'
2. if the ESNs found no records from the table the remarks should be 'No records'
3. if the ESN have the operation type 2 the remarks is 'Not yet returned'

The query works perfectly with the requirements.
If the result set shows a duplicate ESN that is because there is more than one Operation type Value for an ESN.
If you need a different Result Set then you need to re-assess your business requirement.
The query can be modified according to your business requirement so you need to be sure what you want.

The Duplicate ESN that you see is because you added an ESN in the #Data1 table with Operation type '2'.
According to your third requirement that ESN is being Selected.

Query is working exactly as per your requirements.
if you need a different result set then the requirements will have ti change.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

mani_12345
Starting Member

35 Posts

Posted - 2012-06-04 : 06:12:08

ya offcourse u have to change logic accrdng to the requrment ..

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-06-04 : 08:45:59

Hi Vinu, thanks for the reply.

Yes, your query is working, but i did not give the clear requirements.
please see my new data.

1.if the ESN record in Data1 have only operation_type 4 and 5 and rma_type=5, the remarks should be 'Cores'---i found out that there are esn have op type 1,2,3,4,5., i would like to get only the ESN
who have the transaction of op type is 4 and 5 only.
2. if the ESNs found no records from the table the remarks should be 'No records'
3. if the ESN have the operation type 2 the remarks is 'Not yet returned'-- even if there are operation type 1,3,2,4,5 these will reflect as 'not yet return'

data:

Create table #Sample
(ESN nvarchar(35))
Insert #Sample (ESN) Values ('268435459404494586') -–this will fall as ‘Not yet return’
Insert #Sample (ESN) Values ('268435459404507597') --this will fall as ‘Cores’
Insert #Sample (ESN) Values ('268435459404494781') --no records..

Create table #Data1
(ESN nvarchar(35), operation_type int, rma_type int)
Insert #Data1 (ESN,operation_type, rma_type) Values ('268435459404494586',4, 5)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404494586',5, 5)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404494586',1, NULL)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404494586',2, NULL)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404494586',3, NULL)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404507597',5,5)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404507597',4,5)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources