asked on

SQL Query Question

I have one table, dbo.pricing_levels with the following data:

id (pk) item pricing_level_id price
1000 XYZ 1 $10
1000 XYZ 2 0

I want to query to find every item where we have a positive value in with a pricing_leve_id = 1 and a zero value in a pricing_level=2

Walter Ritzel

select *
from dbo.pricing_levels here (price > 0 and priicng_level_id = 1) or (price = 0 and pricing_level_id = 2)

Open in new window

Allan Reitan

1. SELECT DISTINCT A.[id(pk)]
,A.[item]
,A.[pricing_level_id]
,A.[price]
FROM [dbo].[pricing_levels] A CROSS JOIN [dbo].[pricing_levels] B
WHERE A.pricing_level_id < B.pricing_level_id AND A.[price] > 0;

Another Option without the DISTINCT Clause:

2. SELECT A.[id(pk)]
,A.[item]
,A.[pricing_level_id]
,A.[price]
FROM [dbo].[pricing_levels] A CROSS JOIN [dbo].[pricing_levels] B
WHERE A.pricing_level_id < B.pricing_level_id AND A.[price] > 0
GROUP BY A.[id(pk)]
,A.[item]
,A.[pricing_level_id]
,A.[price];

JestersGrind

You can do this with a self join.

SELECT *
FROM pricing_levels a INNER JOIN pricing_levels b ON a.[id(pk)] - b.[id(pk)]
WHERE a.pricing_level_id = 1 AND b.pricing_level_id = 2 AND a.price > 0 AND b.price = 0

Open in new window

Hope this helps,

Greg

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Walter Ritzel

Just as curiosity: care to explain why have you selected this solution and not the others?