MySQL SELECT multiple rows with same column value based on value from another column

Question

I am just getting started with MySQL queries, and have been fighting this for days with no luck finding a solution, so here goes.

Table structure as follows:

|         ID         |   TAG
| 111909684134416384 | WesternU
| 111909684134416384 | ldnon
| 111910470428008448 | ldnont
| 111910470428008448 | fb
| 111910605249712128 | LTC
| 111910605249712128 | ldnon
| 111911886139826176 | ldnont
| 111911886139826176 | WesternU

I would like to select, count, and list the TAG(s) where one TAG of the same ID has 'ldnont' or 'ldnon' listed.

Essentially, from this data set, I would like to list and count:

WesternU (2)
fb (1)
LTC (1)

I've been able to select and count, but only the first row of rows with duplicate ID. Thank-you in advance for any assistance.

Jody · Accepted Answer · 2011-09-25 05:19:49Z

2

 SELECT Tag, COUNT(*) FROM JodyTable 
      WHERE Tag NOT IN ('ldnon', 'ldnont')
         AND ID IN (SELECT ID FROM JodyTable WHERE Tag IN ('ldnon', 'ldnont'))

edited Sep 25, 2011 at 5:19

Jody

391 silver badge6 bronze badges

answered Sep 25, 2011 at 1:54

Larry Lustig

51.2k16 gold badges119 silver badges173 bronze badges

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

1 Comment

Jody Over a year ago

Thank-you! With a little bit of finagling I was able to get this to pull what I was looking for. Here is the final query I have thanks to you. It is very much appreciated. SELECT Tag, COUNT(*) AS cnt FROM JodyTable WHERE Tag NOT IN ('ldnont', 'ldnon') AND ID IN (SELECT ID FROM JodyTable WHERE Tag IN ('ldnont', 'ldnon')) GROUP BY Tag ORDER BY cnt DESC

agoldis · Accepted Answer · 2011-09-25 01:44:28Z

0

If i understand your question correctly - you want to count how many records with the same id has either "ldnon" or "ldnont" tags in table, not counting "ldnon" and "ldnont" themselves

select outer.tag, count(*) as outer.num
from table as outer
where outer.tag NOT = "ldnon" AND outer.tag NOT = "ldnont"  
outer.id IN (
select * from table  as inner
where outer.id = inner.id AND
(inner.tag = "ldnon" OR onner.tag="ldnont")
)
group by outer.tag
order by outer.num

this one is quite complicated maybe, but straightforward.

answered Sep 25, 2011 at 1:44

agoldis

1,08716 silver badges29 bronze badges

2 Comments

Jody Over a year ago

Thanks for the suggestion, but the above answered seemed a little more straight forward. Would there be any speed benefit going with your answer VS Larry's?

agoldis Over a year ago

it's the same, just few shortcuts used instead of explicit conditions list. enjoy :)

agoldis · Accepted Answer · 2011-09-25 01:51:35Z

0

select  t1.tag, count(*)
from table as t1, table as t2 
where 
t1.id = t2.id AND t1.tag NOT = t2.tag AND 
t1.tag NOT = "ldnon" AND t1.tag NOT = "ldnont" AND
(t2.tag = "ldnon" OR t2.tag = "ldonont")
group by t1.tag

answered Sep 25, 2011 at 1:51

agoldis

1,08716 silver badges29 bronze badges

Collectives™ on Stack Overflow

MySQL SELECT multiple rows with same column value based on value from another column

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related