Jerry Swanson <[EMAIL PROTECTED]> wrote on 03/29/2005 11:43:56 AM:
> I want to get everything from user than if record exist in admin so
> user has admin(administrator) in table user with user.id =
> admin.admin_id, so I need to get 'admin' first_name and last_name
>
> If there is no record in table admin with adin.user_id = user.id ,
> than I need at least all records from user
>
> Table: user
> | id | int(10) | | PRI | NULL
> | auto_increment |
> | email | varchar(100) | YES | | NULL
> | |
> | password | varchar(45) | YES | | NULL
> | |
> | first_name | varchar(100) | YES | | NULL
> | |
> | last_name | varchar(100) | YES | | NULL
> | |
> | type | enum('admin','user') | YES | | NULL
> | auto_increment |
>
> Table: admin
> | id | int(10) | | PRI | NULL | auto_increment |
> | admin_id | int(10) | YES | | NULL | |
> | user_id | int(10) | YES | | NULL | |
> | date | datetime | YES | | NULL | |
>
I think I understand your need: for all users, list the user's name and
the name of their administrator, if an administrator exists
SELECT u.type
, u.first_name
, u.last_name
, au.first_name as admin_first
, au.last_name as admin_last
FROM user u
LEFT JOIN admin a
on a.user_id = u.id
LEFT JOIN user au
on au.id = a.admin_ID;
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
