Return rows based on multiple columns

Question

I am a novice Microsoft Query user. I have a table with info like

ckno    ckdate    type  
123     12/1/12     PV  
654     11/6/12     EP
123     12/14/12    EP
852     01/3/13     PV

I want to return all the rows with a type of PV or EP where the ck# are the same (rows 1 and 3). What does the sql look like for this.

What have you tried?

ruakh
– ruakh

2013-03-11 20:28:21 +00:00
Commented Mar 11, 2013 at 20:28 — ruakh
– ruakh, Commented Mar 11, 2013 at 20:28

Jason Whitish · Accepted Answer · 2013-03-11 20:52:43Z

1

You can try this, which should allow you to have multiple differences in type:

    SELECT *
    FROM SomeTable t
    WHERE EXISTS 
        (SELECT 1 FROM SomeTable x 
        WHERE x.ckno = t.ckno AND x.type <> t.type)

answered Mar 11, 2013 at 20:52

Jason Whitish

1,4381 gold badge23 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jason Whitish Over a year ago

I should point out that if [type] can be NULL, then you'd want to be sure to handle that as well (ISNULL around your x.type and t.type is presumably the best way).

Lamak · Accepted Answer · 2013-03-11 20:35:44Z

0

SELECT *
FROM YourTable T
WHERE EXISTS (  SELECT ckno 
                FROM YourTable
                WHERE [type] IN ('PV','EP')
                AND ckno = T.ckno
                GROUP BY ckno
                HAVING COUNT(*)>1)

answered Mar 11, 2013 at 20:35

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Collectives™ on Stack Overflow

Return rows based on multiple columns

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related