Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-06-10
9
Medium Priority
?
721 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 8

Accepted Solution

by:
srnar earned 600 total points
ID: 21750573
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 200 total points
ID: 21750690
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
ID: 21750762
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Putoch
ID: 21751334
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
 
LVL 8

Expert Comment

by:srnar
ID: 21751634
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
ID: 21758085
yes
0
 

Author Comment

by:Putoch
ID: 21758439
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
ID: 21758484
I'm fine. Thanks.
0
 
LVL 2

Expert Comment

by:vasureddym
ID: 21758514
fine with me too.

Thanks
Srinivas
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

722 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