Select count(id) from one table where that id is present in another table MYSQL

Question

EDIT: Detailed description

Detailed Description:

*in table_a there are 100 members but only 50 of them have records in table_b and only 25 have records in table_b where approved = 1 THEREFORE the value I will need returned by the query is 25*

Hey everyone here is the query I am trying to resolve it will need to return a single result count so I can access with mysql_result($query, 0).

SELECT COUNT(id) FROM table_a WHERE (THIS IS WHERE I AM STUCK)

I need to check if the( count of memberID in table_b WHERE memberID matching each id in table_a and approved in table_b = 1) - is greater than 1

The final result needs to be a count of the number of members that have an entry in table_b.

Sample of table columns that need to access

table_a ----------------- id table_b ------------------ id memberID approved

Let me know if you need any more details.

ok are you looking for the ID in table a greater than 1 or in both tables greater than 1? — Jack
– Jack, Commented Apr 20, 2012 at 2:38
Is it correct to say the result should be the number of rows in a that have a corresponding entry in b where (a.id = b.memberId and b.approved = 1)? — sharakan
– sharakan, Commented Apr 20, 2012 at 14:39
@ukritic - Are your final needs to return the "count(table_a.id = table_b.MemberID AND table_b.approved = 1)" value, and only if that count is ">1"? — dan_linder
– dan_linder, Commented Apr 20, 2012 at 15:10
Here is the best way to psudo code the statement "I need to SELECT COUNT(id) FROM table_a WHERE eachID in table_a has an entry in table_b matched to memberID in table_b and approved = 1 in table_b" THe final result should be a single row with COUNT(id) = ## — MadScientist
– MadScientist, Commented Apr 20, 2012 at 19:57

ypercubeᵀᴹ · Accepted Answer · 2012-04-20 20:41:35Z

3

Problem Solved

Had to think of it backwards

 SELECT COUNT( DISTINCT memberID ) FROM table_b WHERE approved =1

I do not need to even look at table_a seeing as I am counting the memberID based on table_b

Sometimes the solution is so simple and right in front of you.

Thanks for all the help! I hope this helps other in the future.

edited Apr 20, 2012 at 20:41

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

answered Apr 20, 2012 at 20:27

MadScientist

5651 gold badge4 silver badges17 bronze badges

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

3 Comments

ypercubeᵀᴹ Over a year ago

This is the same as your question specification with one tiny but crucial detail: Any value of table_b.memberid also appears in table_a.id. Which off course no-one can know, if you don't tell them.

MadScientist Over a year ago

this query returns the result I am looking for. Basically I do not need to query table_a because I only need a count of the DISTINCT memberID's from table_b. After I rethought what information I really needed it became clear to me that table_a was not needed in the query.

ypercubeᵀᴹ Over a year ago

Yes, I meant that it is correct. Also removed the unneeded parentheses after DISTINCT.

davesnitty · Accepted Answer · 2012-04-20 02:27:48Z

I'm assuming you want to get the number of records in table_a that match to table_b, as long as the value in table_b is approved. Therefore, I would join the 2 tables. The inner join ensures that you only consider rows that are in both tables, and the where statement takes care of the approved requirement.

select count(a.id) from table_a a join table_b b on a.id = b.memberID where b.approved=1

This returns the wrong number looks like it returns the total number of items found in table_b not the total from table_a that meet the requirements.
I think this would be correct if you changed COUNT(a.id) to COUNT(DISTINCT a.id)

Jack · Accepted Answer · 2012-04-20 02:32:09Z

0

 SELECT b.ID, a.ID FROM table_a a, table_b b WHERE a.ID = b.ID AND b.ID = 1

edited Apr 20, 2012 at 2:32

answered Apr 20, 2012 at 2:26

Jack

2582 gold badges7 silver badges15 bronze badges

2 Comments

sharakan Over a year ago

According to the OP, he needs entries where "...approved in table_b = 1". ie, b.APPROVED = 1, whereas you have b.ID = 1.

dan_linder Over a year ago

Agreed with @sharakan - the test is for "table_b.approved" = 1, not "b.ID".

ypercubeᵀᴹ · Accepted Answer · 2012-04-20 20:25:56Z

SELECT COUNT(*) AS cnt FROM table_a AS a WHERE EXISTS ( SELECT * FROM table_b AS b WHERE b.memberID = a.id AND b.approved = 1 )

dan_linder · Accepted Answer · 2012-04-20 21:25:50Z

I'm a bit rusty in SQL, but can this be achieved by specifying the two tables in the query like this:

SELECT COUNT(DISTINCT table_a.id) FROM table_a, table_b WHERE table_a.id = table_b.MemberID AND table_b.approved = 1;

I believe that meets your select criteria.

this returns the same number from above what looks like the total count of table_b records and not the count of table_a records that meets the criteria

Collectives™ on Stack Overflow

Select count(id) from one table where that id is present in another table MYSQL

5 Answers 5

3 Comments

2 Comments

2 Comments

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

2 Comments

2 Comments

Comments

1 Comment

Related