Solved

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

Posted on 2008-06-10
9
715 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
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 50 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
 

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

912 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

17 Experts available now in Live!

Get 1:1 Help Now