Solved

Using Case statement to avoide double rows of info T-SQL

Posted on 2008-06-10
9
714 Views
Last Modified: 2012-06-27
Hi I have one table  made up of a sub-query that holds coverage address

I want to query this coverage_check table against my db (which holds different tables with Address info and customer info_)
I want to compare the these tables using the town and county as links.

So in the coverage check table, I want to see if there is a town and county the same match in the other db tables.

Here is an example of my tables.
Instead of getting

123, Fenniscourt, Bagenalstown,Carlow, Null,Null, Ripwave and Breeze, Bagenalstown, Carlow
124, High Street, Bagenalstown,Carlow,Ripwave,Null,Null, Bagenalstown,Carlow

I am getting :
123,      Fenniscourt,Bagenalstown,Carlow,NULL,Breeze,Ripwave and Breeze,Bagenalstown,Carlow
123,      Fenniscourt,Bagenalstown,Carlow,Ripwave,NULL,Ripwave and Breeze,Bagenalstown,Carlow
124,      High Street,Bagenalstown,Carlow,NULL,Breeze,Ripwave and Breeze,Bagenalstown,Carlow
124,      High Street,Bagenalstown,Carlow,Ripwave,NULL,Ripwave and Breeze,Bagenalstown,Carlow
                 

I think this is because of my product_groupings but I do not know how to get the restult on top
Can someone plese advise?
thanks

Here is an example of my tables. 
 

CREATE TABLE [dbo].[COVERAGE_CHECK](

      [id] [bigint] NULL,

      [orderID] [bigint] NULL,

      [address_1] [nvarchar](255) NULL,

      [address_2] [nvarchar](255) NULL,

      [address_3] [nvarchar](255) NULL,

      [address_4] [nvarchar](255) NULL,

      [town] [nvarchar](200) NULL,

      [county] [nvarchar](100) NULL

) ON [PRIMARY]
 
 

INSERT INTO [COVERAGE_CHECK] values (1,123,'Fenniscourt',null,null,null,'Bagenalstown','Carlow')

INSERT INTO [COVERAGE_CHECK] values (2,124,'High Street',null,null,null,'Bagenalstown','Carlow')
 

Drop Table Account

Create Table Account (

account_id int,

product_id int, 

customer_id int)

insert into account values (621681,7,1111)

insert into account values (345654,7,1101)

insert into account values (345677,7,1112)

insert into account values (347777,8,1112)
 

Drop Table Customer

Create Table  Customer(

customer_id int,

address_1 varchar(30),

address_2 varchar(30),

address_3 varchar(30),

town varchar(50),

county varchar(30))

insert into customer values(1111,'Fenniscourt',null,null,'Bagenalstown','Carlow')

insert into customer values(1112,'Fenniscourt',null,null,'Bagenalstown','Carlow')

insert into customer values(1101,'The railwayhouse',null,null,'Bagenalstown','Carlow')
 
 

Drop Table Product

create table  product(

product_id int,

product varchar(30))

insert into Product values(7,'Ripwave')

insert into Product values(8,'Breeze')
 
 

SELECT    

 distinct COVERAGE_CHECK.orderID,

 COVERAGE_CHECK.address_1,

 COVERAGE_CHECK.town, COVERAGE_CHECK.county,

CASE when  product.product_id = 7 then 'Ripwave' Else Null End RipwaveProduct,

Case When product.product_id = 8 then 'Breeze' ELse Null End BreezeProduct,

Case When product.product_id in (7,8) then 'Ripwave and Breeze' else Null End BothProd,

		                    --  Customer.address_1 AS Expr1, 

					Customer.town AS Expr2, Customer.county AS Expr3

FROM         Customer 

INNER JOIN          Account ON Customer.customer_id = Account.customer_id 

INNER JOIN          product ON Account.product_id = product.product_id 

Right Outer  JOIN

                      COVERAGE_CHECK ON --Customer.address_1 = COVERAGE_CHECK.address_1 

					 Customer.town = COVERAGE_CHECK.town AND 

                      Customer.county = COVERAGE_CHECK.county
 
 

Instead of getting 
 

123, Fenniscourt, Bagenalstown,Carlow, Null,Null, Ripwave and Breeze, Bagenalstown, Carlow

124, High Street, Bagenalstown,Carlow,Ripwave,Null,Null, Bagenalstown,Carlow
 

I am getting :

123,	Fenniscourt,Bagenalstown,Carlow,NULL,Breeze,Ripwave and Breeze,Bagenalstown,Carlow

123,	Fenniscourt,Bagenalstown,Carlow,Ripwave,NULL,Ripwave and Breeze,Bagenalstown,Carlow

124,	High Street,Bagenalstown,Carlow,NULL,Breeze,Ripwave and Breeze,Bagenalstown,Carlow

124,	High Street,Bagenalstown,Carlow,Ripwave,NULL,Ripwave and Breeze,Bagenalstown,Carlow

Open in new window

0
Comment
Question by:Putoch
  • 4
  • 3
  • 2
9 Comments
 
LVL 8

Accepted Solution

by:
srnar earned 150 total points
Comment Utility
With this this content of tables you are not able to get first row with two NULL (RipwaveProduct, BreezeProduct) because there are too many cross joins (via town, county). You can get better "compression" via group by - see second script.
--visible multiplication script

SELECT    

*

FROM         Customer2 Customer

INNER JOIN          Account ON Customer.customer_id = Account.customer_id 

INNER JOIN          product ON Account.product_id = product.product_id 

Right Outer  JOIN

                      COVERAGE_CHECK ON --Customer.address_1 = COVERAGE_CHECK.address_1 

                                         Customer.town = COVERAGE_CHECK.town AND 

                      Customer.county = COVERAGE_CHECK.county
 
 
 

--group by script

SELECT    

 COVERAGE_CHECK.orderID,

 COVERAGE_CHECK.address_1,

 COVERAGE_CHECK.town, 

 COVERAGE_CHECK.county,

 CASE WHEN 

		SUM(CASE when product.product_id = 7 then 1 Else 0 END)>0

	THEN 'Ripwave'

	ELSE NULL

 end

 RipwaveProduct,

 CASE WHEN 

		SUM(Case When product.product_id = 8 then 1 ELse 0 END)>0

	THEN 'Breeze'

	ELSE NULL

 END

 BreezeProduct,

 CASE WHEN 

	SUM(Case When product.product_id in (7,8) then 1 else 0 End)>0                                        

	THEN 'Ripwave and Breeze'

	ELSE NULL

 END BothProd,

 Customer.town AS Expr2, 

 Customer.county AS Expr3 

FROM         Customer2 Customer

INNER JOIN          Account ON Customer.customer_id = Account.customer_id 

INNER JOIN          product ON Account.product_id = product.product_id 

Right Outer  JOIN

                      COVERAGE_CHECK ON --Customer.address_1 = COVERAGE_CHECK.address_1 

                                         Customer.town = COVERAGE_CHECK.town AND 

                      Customer.county = COVERAGE_CHECK.county

                      

GROUP BY  COVERAGE_CHECK.orderID,

 COVERAGE_CHECK.address_1,

 COVERAGE_CHECK.town, 

 COVERAGE_CHECK.county,

 Customer.town,

 Customer.county

Open in new window

0
 
LVL 2

Assisted Solution

by:vasureddym
vasureddym earned 50 total points
Comment Utility
For the example you have given, the COVERAGE_CHECK table is having the same town and country. only if the address_1 is considered, we can get the desired results.
change#1) so i have modified the third insert into customer query to insert the same 'High Street' instead of 'The railwayhouse'.
With this change made and some more changes as mentioned below, i can get you the results that you want.

change#2) Some modifications to your final select query
change#3) also, i have added a new query to the end (broke the final query into 2 steps)
see the code

INSERT INTO [COVERAGE_CHECK] values (2,124,'High Street',null,null,null,'Bagenalstown','Carlow')
 
 

SELECT    

 distinct COVERAGE_CHECK.orderID,

 COVERAGE_CHECK.address_1,

 COVERAGE_CHECK.town, COVERAGE_CHECK.county,

 product.product,

 Customer.address_1 AS Expr1, 

 Customer.town AS Expr2, Customer.county AS Expr3

INTO #temp

FROM Customer

INNER JOIN Account ON Customer.customer_id = Account.customer_id 

INNER JOIN product ON Account.product_id = product.product_id 

Right Outer  JOIN COVERAGE_CHECK  ON Customer.address_1 = COVERAGE_CHECK.address_1 and Customer.town = COVERAGE_CHECK.town AND Customer.county = COVERAGE_CHECK.county
 

select r.orderid, r.address_1, r.town, r.county

,case when isnull(r.product, '') <> '' and isnull(b.product, '') <> '' then null else r.product end as RipwaveProduct

,case when isnull(r.product, '') <> '' and isnull(b.product, '') <> '' then null else b.product end as BreezeProduct

,case when isnull(r.product, '') <> '' and isnull(b.product, '') <> '' then r.product+' and '+b.product else null end as BothProd

,r.Expr2,r.Expr3

from (

	select * from #temp

	where Product ='Ripwave') as r

full outer join (

	select * from #temp

	where Product = 'Breeze' ) as b 

	on r.orderid = b.orderid and r.town = b.town and r.county = b.county -- you can also add the join on address_1

Open in new window

0
 
LVL 2

Expert Comment

by:vasureddym
Comment Utility
srnar,

your query is returning records as
123,Fenniscourt, Bagenalstown,Carlow, Ripwave, Breeze, Ripwave and Breeze, Bagenalstown,Carlow
124,High Street, Bagenalstown, Carlow, Ripwave, Breeze,Ripwave and Breeze, Bagenalstown, Carlow

where as Putoch want is as
123, Fenniscourt, Bagenalstown,Carlow, Null,Null, Ripwave and Breeze, Bagenalstown, Carlow
124, High Street, Bagenalstown,Carlow,Ripwave,Null,Null, Bagenalstown,Carlow
0
 

Author Comment

by:Putoch
Comment Utility
The problem with using the first address-1 vasureddym is that this is not required or asked for, for the query, by using the address 1 it is stopping more rows from returning .

i am going to test both these queries, and get back to you, thank you for the advise.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 8

Expert Comment

by:srnar
Comment Utility
Thanks vasureddym. I was only trying to rewrite the statement. On the first place is the original (business) need that can be different from Putoch need. Let him decide.
0
 
LVL 2

Expert Comment

by:vasureddym
Comment Utility
yes
0
 

Author Comment

by:Putoch
Comment Utility
Hi Guys, thanks for your help, i tried both your statements and srnar example brought back the results i needed.
Thank you both for your input , time and advice.
I want to split the points as both syntax were valid, but i will give the majority to srnar as i used his syntax, do you both agree with this? if not let me know.

0
 
LVL 8

Expert Comment

by:srnar
Comment Utility
I'm fine. Thanks.
0
 
LVL 2

Expert Comment

by:vasureddym
Comment Utility
fine with me too.

Thanks
Srinivas
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now