CODEWITHSUNDEEP
mysql
MP123
MP123

Reputation: 275

Mysql compare two tables

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 - [email protected] - Seattle - WA - blah,blah,blah  
2 - 1 - Dave - [email protected] - Portland - OR - blah,blah,blah  
3 - 0 - Jake - [email protected] - San Francisco - CA - blah,blah,blah  
4 - 1 - Julie - [email protected] - 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.

Upvotes: 0

Views: 3956

Answers (2)

HJW
HJW

Reputation: 23443

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.

Upvotes: 0

Edgar Velasquez Lim
Edgar Velasquez Lim

Reputation: 2446

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

Upvotes: 2

Related Questions