MySQL using cursor loop

Question

I've got a MySQL table which contains 3 columns, the columns are named the following: TypeID, PropertyID, ValueID.

Let's say the table has the following rows:

| TypeID | PropertyID | ValueID |
| 45     | 266        | 736     |
| 46     | 266        | 736     |
| 50     | 266        | 736     |
| 52     | 266        | 736     |
| 50     | 229        | 628     |
| 52     | 229        | 628     |

I'm looking to select the TypeID when ValueID matches both 736 and 628 which is the case for both 50 and 52.

Please let me know if this needs explaining further.

Group by on TypeID, provide ValueId as IN clause and find distinct count of 2. — skelwa
– skelwa, Commented Aug 2, 2018 at 11:16

Yogesh Sharma · Accepted Answer · 2018-08-02 11:33:33Z

4

You want GROUP BY :

select TypeID
from table t
where ValueID in (736, 628)
group by TypeID
having count(*) = 2;

If you have duplicate ValueID then you need COUNT(DISTINCT ValueID).

If you want to all columns then you can do JOIN :

select t.*
from table t inner join
     (select TypeID
      from table t
      where ValueID in (736, 628)
      group by TypeID
      having count(*) = 2
     ) tt 
    on tt.TypeID = t.TypeID;

edited Aug 2, 2018 at 11:33

answered Aug 2, 2018 at 11:16

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

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

1 Comment

AntBirch Over a year ago

If I wanted to create a new table in MySQL with 3 columns (same as the previous table) to store the results in. How would I go about select TypeID, PropertyID, ValueID as I only want to group by TypeID?

jmsds · Accepted Answer · 2018-08-02 11:38:43Z

0

You can use following query to fetch the records

select TypeID from table where ValueID=736 or ValueID=628
    group by TypeID
    having count(distinct ValueID) > 1;

answered Aug 2, 2018 at 11:38

jmsds

2251 silver badge7 bronze badges

Collectives™ on Stack Overflow

MySQL using cursor loop

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