使用一个表中的数据从另一个表获取数据

Question

我有点困惑。我正在使用SSMS。

"对于客户Ryan Goff的每次预订，请列出预订ID、行程ID和人数">

Ryan Goff在CUSTOMER表中，主键为CUSTOMER_NUM。所有其他都在RESERVATION表中，主键为RESERVATION_ID，外键为CUSTOMER_NUM

我该如何从姓名中获得预订ID、行程ID和人数列表？难道我不需要获取人员CUSTOMER_NUM来连接值吗？我该怎么做？

粗体=主键

客户(CUSTOMER_NUM，LAST_NAME，ADDRESS，CITY，STATE，POSTAL_CODE，PHONE(

保留(RESERVATION_ID、TRIP_ID、TRIP_DATE、NUM_PERSONS、TRIP_PRICE、OTHER_FEES、CUSTOMER_NUM(

Answer 1

您可以将IN运算符与子查询一起使用。

这个确切问题的一个例子是：

SELECT RESERVATION_ID, TRIP_ID, NUM_PERSONS
FROM RESERVATION
WHERE CUSTOMER_NUM IN
(SELECT CUSTOMER_NUM
FROM CUSTOMER
WHERE FIRST_NAME = 'RYAN' AND LAST_NAME = 'Goff');

Answer 2

实现这一点的最佳方法是在外键上连接表，外键将是客户表中的主键。见下文：

SELECT RESERVATION_ID, TRIP_ID, NUM_PERSONS
FROM RESERVATION
INNER JOIN CUSTOMER NUM
ON RESERVATION.CUSTOMER_NUM = CUSTOMER.CUSTOMER_NUM
WHERE CUSTOMER.FIRST_NAME = 'Ryan' AND CUSTOMER.LAST_NAME = 'Goff'