Mysql join query

Question

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?

Steve Mayne · Accepted Answer · 2011-06-01 15:05:03Z

1

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'])
{
   ... 
}

edited Jun 1, 2011 at 15:05

answered Jun 1, 2011 at 14:58

Steve Mayne

23k4 gold badges53 silver badges49 bronze badges

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

Comments

Marc B · Accepted Answer · 2011-06-01 14:59:00Z

0

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

answered Jun 1, 2011 at 14:59

Marc B

362k44 gold badges433 silver badges508 bronze badges

Comments

Brett · Accepted Answer · 2011-06-01 14:59:39Z

0

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

answered Jun 1, 2011 at 14:59

Brett

4,0612 gold badges28 silver badges39 bronze badges

2 Comments

Johan Over a year ago

It's the same syntax in MySQL.

Brett Over a year ago

@Johan - thanks for the clarification, I figured if it wasn't exactly the same it was very close

garnertb · Accepted Answer · 2011-06-01 15:05:21Z

0

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

EDITED

edited Jun 1, 2011 at 15:05

answered Jun 1, 2011 at 14:58

garnertb

9,62439 silver badges38 bronze badges

Comments

Bojan · Accepted Answer · 2011-06-01 15:06:36Z

0

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.

answered Jun 1, 2011 at 15:06

Bojan

1

4 Comments

XCS Over a year ago

I don't actually have an "isPremium" column. And I do this because in some queries I only need to select premium users, so it's better to do a query for only 40-50 users instead of 10.000

Bojan Over a year ago

well you can always create a view for premium users. basicly select * from users where date is not null.

XCS Over a year ago

Yeah, but if I have 1.000.000 users and I do that select it would be very slow. If I have a separate premium table, when I want to see the premium users I only do a select from the users that ARE premium (less then 10% of the users table)

Bojan Over a year ago

your gonna be doing a query on the users table regardless to get usernames. doing the it other way in certain situations you will have to make multiple queries. my was it should be one all the time. plus its simpler.

Collectives™ on Stack Overflow

Mysql join query

5 Answers 5

Comments

Comments

2 Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

2 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related