CODEWITHSUNDEEP
mysqlsqljoin
cookie
cookie

Reputation: 2728

Query a link table using joins in MySQL

My schema is as follows: http://acookson.org/wp-content/uploads/bookings.png

enter image description here

I'm trying to pull unique records using joins. Say 'bob' made a booking on '2013-02-05 13:50:01' with a booking.id=6. How do I query, join and search for a unique record from the lesson table?

My tables are populated with some data:

mysql> SELECT * from user; 
+----+--------+-----------------+
| id | name   | email           |
+----+--------+-----------------+
|  1 | bob    | [email protected]    |
|  3 | sarah  | [email protected]   |
|  4 | phil   | [email protected] |
|  5 | freda  | [email protected] |
|  6 | Sash   | [email protected]     |
|  7 | Glen   | [email protected] |
|  8 | Walter | [email protected]   |
+----+--------+-----------------+
7 rows in set (0.00 sec)

mysql> SELECT * from booking; 
+----+---------------------+---------+
| id | date                | user_id |
+----+---------------------+---------+
|  1 | 2013-02-08 12:28:24 |       1 |
|  4 | 2013-02-07 12:42:02 |       3 |
|  5 | 2013-02-05 12:42:46 |       4 |
|  6 | 2013-02-05 13:50:01 |       1 |
|  7 | 2013-02-01 13:50:01 |       3 |
|  8 | 2013-02-06 13:50:01 |       3 |
|  9 | 2013-01-29 13:50:01 |       4 |
+----+---------------------+---------+
7 rows in set (0.00 sec)

mysql> select * from lesson;
+----+-----------------------------+---------------------+---------------------+
| id | name                        | start_time          | end_time            |
+----+-----------------------------+---------------------+---------------------+
|  2 | CBT course                  | 2013-02-08 12:35:36 | 2013-02-08 13:35:36 |
|  3 | CBT course                  | 2013-02-15 11:59:44 | 2013-02-15 12:59:44 |
|  4 | Advanced Motorcyling module | 2013-02-15 12:04:29 | 2013-02-15 13:04:29 |
|  5 | CBT course                  | 2013-02-15 12:14:27 | 2013-02-15 13:14:27 |
|  6 | ABC course                  | 2013-02-13 13:28:13 | 2013-02-13 14:28:13 |
|  7 | LKU course                  | 2013-02-11 13:28:13 | 2013-02-11 14:28:13 |
|  8 | ERT starter course          | 2013-02-10 13:28:13 | 2013-02-10 14:28:13 |
+----+-----------------------------+---------------------+---------------------+
7 rows in set (0.00 sec)

My 

lesson_booking
table is defined to reduce redundancy and it's this table that I'm trying to query (indirectly) in order to return results.

My query looks like:

SELECT * from user as u
JOIN booking AS b ON b.id = u.id
JOIN lesson_booking AS lb ON b.id = lb.booking_id 
JOIN lesson AS l ON lb.lesson_id = l.id 
WHERE u.name = 'bob';
Empty set (0.00 sec)

but this returns no results. I'm pretty basic with MySQL and so was looking for some examples of how I might query this schema really.

If you could provide me with several (three would do - different) examples of how I might query this data set then that would be an education - I hope!

Upvotes: 4

Views: 13077

Answers (3)

PeterJohn
PeterJohn

Reputation: 589

You can also something like

select * from table1 a, table2 b 
where a.id = b.id

In this way you can link every tables that have relations

Upvotes: -1

John Woo
John Woo

Reputation: 263733

the problem with your current query is that you are joining the ID of user from ID of booking which is wrong. It should be, ID of user from user_ID of booking:

SELECT  a.*, b.*, c.*, d.*
FROM    booking a
        INNER JOIN user b
            ON a.user_ID = b.id
        INNER JOIN lesson_booking c
            ON a.id = c.booking_ID
        INNER JOIN lesson d
            ON c.lesson_ID = d.ID
WHERE   b.name = 'bob'

To fully gain knowledge about joins, kindly visit the link below:

Upvotes: 4

sgeddes
sgeddes

Reputation: 62841

You were close -- look at your join on booking -- use user_id instead of id. It shouldn't be b.id = u.id (this joins your userid to your bookingid), but rather b.user_id = u.id:

SELECT * 
FROM user as u
   JOIN booking AS b ON b.user_id = u.id
   JOIN lesson_booking AS lb ON b.id = lb.booking_id 
   JOIN lesson AS l ON lb.lesson_id = l.id 
WHERE u.name = 'bob';

Good luck.

Upvotes: 4

Related Questions