CODEWITHSUNDEEP
mysql
Scorpioniz
Scorpioniz

Reputation: 1449

mysql need get count of dependencies of other table

have 2 tables:

`contactslistrelations`

(`id`, `contactid`, `contactlistid`)
(1, 1, 1),
(2, 2, 1),
(3, 3, 1),
(4, 4, 1),
(5, 5, 1),
(6, 6, 1),

and another one: 

`contacts`

(`id`, `name`, `surname`, `email`, `unsubscribed`)
(1, "name1", "surname1", "email1", "0"),
(2, "name1", "surname2", "email1", "0"),
(3, "name1", "surname3", "email1", "0"),
(4, "name1", "surname4", "email1", "1"),
(5, "name1", "surname5", "email1", "0"),
(6, "name1", "surname6", "email1", "0"),

And I need to get count from contactlistrelations table of contacts which is not unsubscribed in contacts table

Upvotes: 0

Views: 65

Answers (1)

Kasper Sanguesa-Franz
Kasper Sanguesa-Franz

Reputation: 617

You can use join like this, which should return the number of people on each contactList which isn't unsubscribed 

Select contactlistrelations .contactlistid,count(*) AS count 
  FROM contactlistrelations 
  JOIN contacts on contacts.id = contactlistrelations.contactid 
  WHERE contacts.unsubscribed = 0
  GROUP BY contactlistrelations.contactlistid

Upvotes: 1

Related Questions