CODEWITHSUNDEEP
mysqldatabaseselect
chrismsawi
chrismsawi

Reputation: 145

Relating the values one table to another table

I have two tables. One is named usertbl with columns user_id, user_name, and acct_status. possible values for acct_status values are 0, 1, 2, 3. Another table named accountstatustbl has the following columns - acct_status, and acct_status_desc, with values when acct_status = 0, acct_status_desc = enrolled, 1 = active, 2 = inactive, and 3 = deactivated. Now, how should I query my db to provide me the following output:

user_id     user_name     status
0000000     user1         enrolled
1234567     user2         active
9999999     user3         deactived

instead of giving me values 0, 1, and 3?

Upvotes: 0

Views: 83

Answers (3)

Walter Macambira
Walter Macambira

Reputation: 2605

SELECT u.user_id, u.username, s.acct_status_desc status 
FROM usertbl u, accountstatustbl s
WHERE u.acct_status = s.acct_status

Hope I helped.

Upvotes: 2

Joseph Caracuel
Joseph Caracuel

Reputation: 974

you can use INNER JOIN assuming that acct_status in usertbl has no NULL values:

SELECT a.user_id, 
       a.user_name, 
       b.status_desc AS status 
FROM   usertbl a 
       INNER JOIN accountstatustbl b 
               ON b.acct_status = a.acct_status

you can also read about MySQL JOIN Syntax to understand more about joins

Upvotes: 1

Sachin
Sachin

Reputation: 40970

You can try this.

SELECT user.user_id, user.user_name, status.description
FROM usertbl user left outer join accountstatustbl status
on user.acct_status=status.acct_status

Upvotes: 2

Related Questions