CODEWITHSUNDEEP
mysqlsqljoin
XCS
XCS

Reputation: 28177

Mysql join query

I have table users and another table premium_users in which I hold the userid and the date when he bought premium membership.

How can I use mysql join , so that in a single query I can select all the columns from the table users and also know for each premium user the date he joined on.

USERS:

ID USERNAME
1  JOHN
2  BILL
3  JOE
4  KENNY

PREMIUM USERS:

ID USERID DATE
1  2      20/05/2010
2  4      21/06/2011

And the final table (the one that will be returned my the query) should look like this:

ID USERNAME DATE
1  JOHN
2  BILL     20/05/2010
3  JOE
4  KENNY    21/06/2011

Is it ok for some rows to have the DATE value empty? How can I check if that value is empty? $row['date']=='' ?

EDIT: This was only an example, but the users table has much more columns, how can I select all from users and only date from premium_users without writing all the columns?

Upvotes: 0

Views: 89

Answers (5)

Bojan
Bojan

Reputation: 1

It might be easier to have it all in one table. You can have nullable fields for isPremium(t/f) and premiumDate. you actually dont even need the isPremium field. just premiumDate if its null they are not premium and if it has value they are premium user and you have the date they joined.

Upvotes: 0

garnertb
garnertb

Reputation: 9594

SELECT A.*, B.DATE
FROM USERS A
LEFT JOIN PREMIUIM_USERS B on A.ID=B.USERID

EDITED

Upvotes: 0

Steve Mayne
Steve Mayne

Reputation: 22858

select u.*, pu.DATE
from USERS u LEFT OUTER JOIN PREMIUM_USERS pu on
  u.ID = pu.USERID

You can check if a row is empty with:

if (!$row['DATE'])
{
   ... 
}

Upvotes: 1

Brett
Brett

Reputation: 4061

This is mssql syntax, but it should be pretty similar...

select *
from users u
left join premiumUsers p
on u.id = p.id
order by u.id asc

Upvotes: 0

Marc B
Marc B

Reputation: 360872

select USERS.ID, USERS.USERNAME, PREMIUM_USERS.DATE
from USERS
join PREMIUM_USERS on USERS.ID = PREMIUM_USERS.ID
order by USERS.ID

Upvotes: 0

Related Questions