"Rich" wrote:

> Hello,
>
> I need to retrieve all rows from the following dataset where code1 contains
> only('aa' , 'bb)  and code2 contains only ('c1', 'c2', 'c3') when code1 is
> bb.  So I want to pick up all the rows for code1 = 'aa', and only the rows
> when code1 = 'bb' where code2 contains ('c1', 'c2', 'c3').  How to do this?
>
> CREATE TABLE #temp1(rowNum int, code1 varchar(2), code2 varchar(2))
> INSERT INTO #temp1
> SELECT 1, 'aa', 'b1' union
> SELECT 2, 'aa', 'b2' union
> SELECT 3, 'aa', 'b3' union
> SELECT 4, 'aa', 'b4' union
> SELECT 5, 'bb', 'c1' union
> SELECT 6, 'bb', 'c2' union
> SELECT 7, 'bb', 'c3' union
> SELECT 8, 'bb', 'c4' union
> SELECT 9, 'cc', 'd1' union
> SELECT 10, 'cc', 'd2' union
> SELECT 11, 'cc', 'd3' union
> SELECT 12, 'cc', 'd4'
>
> --Here is my pseudo code
> SELECT * FROM #temp1 WHERE code1 IN ('aa', 'bb')
> AND when code1 = 'bb' then code2 In ('c1', 'c2', 'c3')
>
> I am thinking a Case statement maybe in the where clasue?
>
> Any suggestions greatly appreciated.
>
> Thanks,
> Rich
>
>

"Scott Morris" wrote:

> "Rich" <R***@discussions.microsoft.com> wrote in message
> news:46E1791C-46B4-4268-9592-537FD236D59C@microsoft.com...
> > Well, I came up with this:
> >
> > SELECT * FROM #temp1
> > WHERE code1 = 'aa'
> > Union
> > SELECT * FROM #temp1
> > WHERE code1 = 'bb' AND code2 IN ('c1', 'c2', 'c3')
> >
> > Using the Union operator.  I vaguely recall having to do this before.  I
> > believe the trick is to use the Union operator.
>
> The logical solution would be:
> where code1 = 'aa' or (code1 = 'bb' and code2 in ('c1', 'c2', 'c3'))
>
> Review the query plans for each to determine which is more efficient for
> your particular situation.  You should also use "union all" to avoid
> pointless work within the engine (assuming it can't short circuit the
> duplicate removal logic in this case based on the use of constants - I don't
> think it can).
>
>
>