SQL possibly JOIN problem

[metrostars]

Hi.

 

I 2 tables like this.

 

users

 

id                    1                  2

firstname          Bob              Jimmy

lastname        Hendrix          Marley

...

 

and

 

bookings

 

id                    1                      2

userid              2                      2

date          2008-08-12        2008-08-15

...

 

What I would like is to select a number of fields from users and also the id from bookings that links bookings.userid to users.id.

 

So:

 

1                      users.id

Jimmy                firstname

Marley              lastname

2                    bookings.id

 

There must only be 1 id returned for bookings.id per user.

 

I've tried a number of strawberry mthods but i can't the right one.

 

Thanks

[Barand]

SELECT u.id, u.firstname, u.lastname, b.id as bookingid
FROM users u INNER JOIN bookings b ON u.id = b.userid

 

As we have 2 "id" cols in the results the second is given a column alias to distinguish between them

[Barand]

I've just noticed the single booking id constraint

 

SELECT u.id, u.firstname, u.lastname, x.id as bookingid
FROM users u 
    INNER JOIN  (SELECT userid, id FROM bookings GROUP BY userid) as x
    ON u.id = x.userid