Mysql compare two tables

0 votes
asked Jul 20, 2011 by mp123

I have two tables in the same database with matching id's.

The first table is accounts

id - account_access - name - email - city - state - etc...  
1 - 1 - Mark - mark@mark.com - Seattle - WA - blah,blah,blah  
2 - 1 - Dave - dave@dave.com - Portland - OR - blah,blah,blah  
3 - 0 - Jake - jake@jake.com - San Francisco - CA - blah,blah,blah  
4 - 1 - Julie - julie@julie.com - Los Andeles - CA - blah,blah,blah

The second table is members

 id - status - score - IQ  
 1 - 4 - 9000 - 108  
 2 - 5 - 8000 - 90  
 3 - 5 - 1000 - 25 (Disclaimer: Any relation to real people is purely coincidental)  
 4 - 5 - 5000 - 60

Since Jake wasn't very bright, he has zero access to the account.

What I want to know is how many people with account_access (1) have status (5), or how many people with status (5) have access.
I'm not familiar with how to word the SELECT statement comparing two tables, i would like to know number of rows and then to also retrieve the data to display.

2 Answers

+2 votes
answered Jul 20, 2011 by edgar-velasquez-lim

What you want to do is a left join.

SELECT count(*)
FROM Accounts
LEFT JOIN members
ON Accounts.id = members.id
WHERE status = 5
AND account_access = 1

Additional info on Left Joins: http://www.w3schools.com/sql/sql_join_left.asp

0 votes
answered Jul 20, 2011 by oh-chin-boon

Select count(*) from accounts left join members using (id) where account access = 1 and status = 5;

I don't have the underscore in my bb, so you may have to substitute the space between account and access with an underscore before running the query.

Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...