Complicated SQL Join Query

Question

Hello, i have two tables one of is users:

id      int(11)     NO  PRI     NULL    auto_increment
membership_type     tinyint(3) unsigned     NO  MUL     NULL     
username    varchar(16)     NO      NULL     
password    char(40)    NO      NULL     
email   varchar(100)    NO      NULL     
firstname   varchar(40)     NO      NULL     
lastname    varchar(40)     NO      NULL     
company     varchar(120)    NO      NULL     
birthday    date    NO      NULL     
country     smallint(5) unsigned    NO      NULL     
city    smallint(5)     NO      NULL     
address     varchar(200)    NO      NULL     
landphone   char(10)    NO      NULL     
mobilephone     char(10)    NO      NULL     
website     varchar(150)    NO      NULL     
feedback_score  tinyint(4)  NO      NULL     
created     datetime    NO      NULL     
last_login  datetime    NO      NULL     
last_ip     varchar(15)     NO      NULL     

another's name is user_cache:

user_id     int(11)     NO  MUL     NULL     
cache_type  int(50)     NO      NULL     
value   varchar(100)    NO      NULL     

I want to select user data(membership, username, firstname, lastname etc..) and select related user's cache data(select * from user_cache where user_id = X). I select user with simple sql query then i use another sql query for get cache details. I want to join queries into one query. Is this possible ?

Answer 1

select u.*, uc.*
from users as u
left join user_cache as uc
    on u.id = uc.user_id

You'll get all of the user information for each row of user_cache data and if a user has no user_cache data, then you'll get nulls for the user_cache data.

Answer 2

Maybe I'm not understanding your question correctly, but the following query should return what you need:

SELECT u.membership_type, u.firstname, ..., uc.*
FROM users u
LEFT JOIN user_cache uc
ON u.id = uc.user_id;

Answer 3

I suppose this might work?

SELECT u.*, uc.*
FROM user u
   left join user_cache uc ON uc.user_id = u.id