i need your help to do a specific select

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

SQL Server 2008 Forums

Transact-SQL (2008)

i need your help to do a specific select

Author

Topic

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-08-15 : 18:37:53

hi there

i have a able called "bills"

CREATE TABLE [dbo].[bills](
[idbill] [int] NULL,
[productname] [varchar](50) NULL,
[city] [varchar](50) NULL
) ON [PRIMARY]

GO

and lets put some records

INSERT INTO [dbo].[bills]
([idbill]
,[productname]
,[city])
select 1, 'apple', 'miami'
union
select 1, 'cherry', 'miami'
union
select 2, 'apple', 'NY'
union
select 3, 'apple', 'LA'
union
select 3, 'cherry', 'LA'
union
select 4, 'apple', 'washington'
union
select 7, 'strawberry', 'washington'

and i need to do a select with a column to identify if in the same bill there are "apple" and "cherry" product becase if there are these 2 items in the same bill and the city<> 'miami' i want to identify as a "combo"

that the result that i want , and i need

idbill productname city identify
1 apple miami not combo
1 cherry miami not combo
2 apple NY not combo
3 apple LA combo
3 cherry LA combo
4 apple washington not combo
7 strawberry washington not combo

many many thanks for your help

kind regards

chadmat
The Chadinator

1974 Posts

Posted - 2012-08-15 : 18:52:49

I think this table is in need of some normalization.

-Chad

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-08-15 : 21:33:15

hi chad coul you explain how to do it

im a newbie

thanks in advanced

chadmat
The Chadinator

1974 Posts

Posted - 2012-08-15 : 21:48:57

I would need a lot more information, but there should probably be a Product Table, a Bill Table, maybe a customer table etc... Bing 3rd normal form, or database normalization.

-Chad

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-15 : 22:50:18

[code]
SELECT t.*,
CASE WHEN t1.idbill IS NOT NULL THEN 'Combo' ELSE 'Non Combo' END
FROM Table t
LEFT JOIN (SELECT idbill
FROM Table
GROUP BY idbill
HAVING COUNT(DISTINCT CASE WHEN productname IN ('apple','cherry') AND city <> 'miami' THEN productname ELSE NULL END) =2
)t1
ON t1.idbill = t.idbill
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-08-15 : 23:01:49

always you, always you visakh16 with your magic

once again a fantastic code

many many thanks

thanks for share your knowledge

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-15 : 23:23:27

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

suraz
Starting Member

2 Posts

Posted - 2012-08-16 : 07:20:39

##### Code Optimized #####

SELECT t.*,
CASE WHEN t1.idbill IS NOT NULL THEN 'Combo' ELSE 'Non Combo' END
FROM bills t
LEFT JOIN (SELECT idbill
FROM bills
WHERE city <>'miami'
GROUP BY idbill
HAVING COUNT(idbill)=2)t1 ON t1.idbill = t.idbill

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-08-17 : 13:11:27

thanks you suraz for your help

kund regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-17 : 13:21:55

quote:
Originally posted by suraz

##### Code Optimized #####

SELECT t.*,
CASE WHEN t1.idbill IS NOT NULL THEN 'Combo' ELSE 'Non Combo' END
FROM bills t
LEFT JOIN (SELECT idbill
FROM bills
WHERE city <>'miami'
GROUP BY idbill
HAVING COUNT(idbill)=2)t1 ON t1.idbill = t.idbill

how is this satisfying original requirement? this is not even looking for productname for apple and cherry items

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-08-18 : 09:59:01

hi visakh16

its true the code from suraz its not as complete as yours for my requiremets, but he tried to help me too with his answer..
so im just saying thank you

thanks again visakh16 for your great reply , really helps to me your code

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-18 : 11:09:11

quote:
Originally posted by sebastian11c

hi visakh16

its true the code from suraz its not as complete as yours for my requiremets, but he tried to help me too with his answer..
so im just saying thank you

thanks again visakh16 for your great reply , really helps to me your code

Nope I was not commenting on that

I was just checking with Suraj to confirm whether I'm missing something in his suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

suraz
Starting Member

2 Posts

Posted - 2012-08-20 : 00:48:45

hi visakh16,

both queries returning the same result as sebastian11c's requirement and table/data structure.

Difference is only this:
Your query exactly meets the sebastian11c's requirement for the product "apple" and "cherry" and city<> 'miami' but it won't work when you want same result for other more products. Suppose you need to do same for strawberry then you add strawberry in your query i.e. every time need to change in query

You can do the same for thousand of products by using updated query and require no changes in query.

###################################################################
Thanks to All :)

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2012-08-25 : 21:56:55

quote:
Originally posted by suraz

hi visakh16,

both queries returning the same result as sebastian11c's requirement and table/data structure.

Difference is only this:
Your query exactly meets the sebastian11c's requirement for the product "apple" and "cherry" and city<> 'miami' but it won't work when you want same result for other more products. Suppose you need to do same for strawberry then you add strawberry in your query i.e. every time need to change in query

You can do the same for thousand of products by using updated query and require no changes in query.

###################################################################
Thanks to All :)

The problem is that the OP was very specific about the conditions that make up a combo. They must have both "apple" and "cherry" and not be "Miami". If we change the sample data a bit, we can see that your code doesn't meet that requirement.

INSERT INTO [dbo].[bills]
([idbill]
,[productname]
,[city])
select 1, 'apple', 'miami'
union
select 1, 'cherry', 'miami'
union
select 2, 'apple', 'NY'
union
select 3, 'apple', 'LA'
union
select 3, 'peach', 'LA' --Changed but still comes up as "Combo"
union
select 4, 'apple', 'washington'
union
select 7, 'strawberry', 'washington'

--Jeff Moden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-09-03 : 09:41:40

quote:

Originally posted by visakh16


SELECT t.*,
CASE WHEN t1.idbill IS NOT NULL THEN 'Combo' ELSE 'Non Combo' END
FROM Table t
LEFT JOIN (SELECT idbill
           FROM Table
           GROUP BY idbill
           HAVING COUNT(DISTINCT CASE WHEN productname IN ('apple','cherry') AND city <> 'miami' THEN productname ELSE NULL END) =2
          )t1
ON t1.idbill = t.idbill

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

If you use SUM(CASE WHEN ... THEN 1 ELSE 0 END)... you can avoid the unneccesary warning about NULL

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-03 : 15:24:22

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

SELECT t.*,
CASE WHEN t1.idbill IS NOT NULL THEN 'Combo' ELSE 'Non Combo' END
FROM Table t
LEFT JOIN (SELECT idbill
FROM Table
GROUP BY idbill
HAVING COUNT(DISTINCT CASE WHEN productname IN ('apple','cherry') AND city <> 'miami' THEN productname ELSE NULL END) =2
)t1
ON t1.idbill = t.idbill

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

If you use SUM(CASE WHEN ... THEN 1 ELSE 0 END)... you can avoid the unneccesary warning about NULL

Madhivanan

Failing to plan is Planning to fail

yep i know that
Anyways thnx for reminding

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources