使用MySQL中的连接查询链接表

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

我的架构如下:http://acookson.org/wp-content/uploads/bookings.png

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?

我正在尝试使用连接来提取唯一记录。假设'bob'在'2013-02-05 13:50:01'上预订,预订.id = 6。如何从课程表中查询,加入和搜索唯一记录?

My tables are populated with some data:

我的表中填充了一些数据:

mysql> SELECT * from user; 
+----+--------+-----------------+
| id | name   | email           |
+----+--------+-----------------+
|  1 | bob    | [e-mail]    |
|  3 | sarah  | [e-mail]   |
|  4 | phil   | [e-mail] |
|  5 | freda  | [e-mail] |
|  6 | Sash   | [e-mail]     |
|  7 | Glen   | [e-mail] |
|  8 | Walter | [e-mail]   |
+----+--------+-----------------+
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.

但这不会返回任何结果。我对MySQL非常基本,所以我正在寻找一些如何查询这个模式的例子。

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!

如果你可以向我提供几个(三个会做 - 不同的)例子,我将如何查询这个数据集,那么这将是一个教育 - 我希望!

3 个解决方案

#1

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:

你很亲密 - 看看你的预订加入 - 使用user_id而不是id。它不应该是b.id = u.id(这会将你的用户ID加入你的预订),而是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.

#2

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:

您当前查询的问题是您从预订ID中加入了用户ID,这是错误的。它应该是来自预订的user_ID的用户ID:

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:

要充分了解联接知识,请访问以下链接:

• Visual Representation of SQL Joins

SQL连接的可视化表示

#3

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

通过这种方式,您可以链接每个具有关系的表