Select Statement with multiple logic

Question

Post reply

Select Statement with multiple logic

CoryJ72

SSC Enthusiast

Points: 110
More actions
February 3, 2012 at 3:18 am

#250846

Hi
I would like to create a select statement that looks at the below table and looks at the Product code first then looks at the description and selects the items only if X Large and XX Large are present also XX Large cost = 25
Table1
Product Code Description Cost
1001 Item 1 Small 1
1001 Item 1 Large 5
1001 Item 1 X Large 10
1001 Item 1 XX Large 25
1002 Item 2 Small 1
1002 Item 2 Large 2
1002 Item 2 XX Large 25
1003 Item 3 Small 1
1003 Item 3 Large 2
1003 Item 3 X Large 10
1003 Item 3 XX Large 15
Expected result
Product Code Description Cost
1001 Item 1 X Large 10
1001 Item 1 XX Large 25
I would greatly appreciate if someone can help me with this request.
Cheers
Cory J 😀

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26969 More actions · Answer 1

WITH CTE AS (

SELECT Product,Code,Description,Cost,

COUNT(*) OVER(PARTITION BY Product,Code) AS cn

FROM Table1

WHERE Description='X Large'

OR (Description='XX Large' AND Cost=25))

SELECT Product,Code,Description,Cost

FROM CTE

WHERE cn=2;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

CoryJ72 SSC Enthusiast Points: 110 More actions · Answer 2

Sorry but it does not seem to work. Just to clarify The [Product Code] is the '1001' number and the [Description] is 'Item 1 XX Large' not sure if this makes any difference.

Mark Cowne One Orange Chip Points: 26969 More actions · Answer 3

CoryJ72 (2/3/2012)
Sorry but it does not seem to work. Just to clarify The [Product Code] is the '1001' number and the [Description] is 'Item 1 XX Large' not sure if this makes any difference.

This then?

WITH CTE AS (

SELECT [Product Code],Description,Cost,

COUNT(*) OVER(PARTITION BY [Product Code]) AS cn

FROM Table1

WHERE Description='Item 1 X Large'

OR (Description='Item 1 XX Large' AND Cost=25))

SELECT [Product Code],Description,Cost

FROM CTE

WHERE cn=2;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

CoryJ72 SSC Enthusiast Points: 110 More actions · Answer 4

CoryJ72

SSC Enthusiast

Points: 110

February 3, 2012 at 5:01 am

#1442326

That worked Thank you very much :w00t:

jonmoli Newbie Points: 9 More actions · Answer 5

Or maybe this? 😎

select [Product Code], Description, Cost from Table1

where Description = 'Item 1 X Large' or (Description='Item 1 XX Large' and Cost=25)

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

jonmoli (2/3/2012)
Or maybe this? 😎
select [Product Code], Description, Cost from Table1
where Description = 'Item 1 X Large' or (Description='Item 1 XX Large' and Cost=25)

This will retrun both 1001 and 1003.

OP needs only the ones that has both 'Item 1 X Large' and 'Item 1 XX Large' and 'Item 1 XX Large' = 25.

jonmoli Newbie Points: 9 More actions · Answer 7

Hmm...Product Code 1003 has the below possible descriptions, so 1003 will not be retrieved. 🙂

"Item 3 X Large"

"Item 3 XX Large"

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 8

jonmoli (2/3/2012)
Hmm...Product Code 1003 has the below possible descriptions, so 1003 will not be retrieved. 🙂
"Item 3 X Large"
"Item 3 XX Large"

Well then OP does not know what (s)he is after!

His/Her first post

CoryJ72 (2/3/2012)
selects the items only if X Large and XX Large are present also XX Large cost = 25

Item 1 XX Large , Item 3 XX Large seem to be a concatenated values and i beleive that the OP wants XX Large rather than only Item 1 XX Large. Lets wait for Cory (the OP) to clear our minds 😛

CoryJ72 SSC Enthusiast Points: 110 More actions · Answer 9

Sorry for the confusion but what I’m trying to do is to search each line and look at Product Code first then look at all line with same Product Code and then within the descriptions which must have both ‘XX Large’ and ‘X Large’. If this Product Code has both these lines then check price of ‘XX Large’ = 25 then return both lines if not then return neither. I hope that this clears things up.

CoryJ