SQL Query to retrieve rows having conditional values in a column

Question

I don't know if this question has been asked previously also. If so please direct me to the link.

I have a table that has three columns name, type and date. Type can only be 4 values A, B, C and D

I want to fetch all those records which are of type A, B or C but the condition is that it should only fetch if the same name also has a type of D.

e.g. lets consider this table

Name      type    Date 
abc        A       5/7
abc        B       6/7
abc        D       7/7

xyz        A       5/7
xyz        D       6/7

lmn        A       5/7
lmn        B       6/7
lmn        C       7/7

So the deal here I need the following result set

ABC 5/7
ABC 6/7
XYZ 5/7

Because ABC and XYZ has a type D the other records of ABC and XYZ are shown. Since lmn does not have a type D it's not included in the result set.

Denys Séguret · Accepted Answer · 2012-06-07 16:43:18Z

3

To test if a record exist, you can simply use where exists :

select * from mytable t1 where exists (
     select * from mytable t2 where t1.Name=t2.Name and t2.type="D"
);

That's probably self explanatory but here's a reference : http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html

If you want to exclude the D records, you do this :

select * from mytable t1 where t1.type<>"D" and exists (
     select * from mytable t2 where t1.Name=t2.Name and t2.type="D"
);

edited Jun 7, 2012 at 16:43

answered Jun 7, 2012 at 16:30

Denys Séguret

384k90 gold badges813 silver badges780 bronze badges

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

2 Comments

Abe Miessler Over a year ago

I believe this would return 3 records for ABC and 2 records for XYZ.

Denys Séguret Over a year ago

I reread the requirement and you're right. I edited my answer by adding a second query, that's a minor adjustment.

Abe Miessler · Accepted Answer · 2012-06-07 16:31:54Z

1

Try this:

SELECT Name, Date
FROM MyTable as mt
WHERE type != 'D'
AND EXISTS
(
   SELECT * FROM MyTable as mt2
   WHERE mt2.type = 'D' and mt2.name = mt.name
)

You are selecting all records where type is not equal to D and that have a record with a matching name where type IS equal to D

answered Jun 7, 2012 at 16:31

Abe Miessler

85.7k104 gold badges323 silver badges496 bronze badges

Comments

Florin Stingaciu · Accepted Answer · 2012-06-07 16:31:08Z

0

create view all_D as select name from your_table where type=D
select * from your_table where type<>D and name in (select * from all_D)

You could even make it such that instead of having that view you jut put that query in the brackets after "not in"

answered Jun 7, 2012 at 16:31

Florin Stingaciu

8,3532 gold badges26 silver badges45 bronze badges

Collectives™ on Stack Overflow

SQL Query to retrieve rows having conditional values in a column

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related