oracle 如何连接三个表并从三个表和oracle中检索选定的列？

how to join three tables and retrieve selected columns from the three tables and in oracle?

提问by user1261319

I have 3 tables. cinema, booking and customer

我有3张桌子。电影院，预订和客户

create table cinema
(
c_id int,
location varchar(10)
)
insert into cinema values(1,'New York');
insert into cinema values(2,'London');
insert into cinema values(3,'Paris');

create table booking
(
c_id int,
cust_id int
)

insert into booking values(1,10);
insert into booking values(2,11);
insert into booking values(3,12);
insert into booking values(3,13);
insert into booking values(2,14);

create table customer
(
cust_id int,
cust_name varchar(10)
)

insert into customer values(10,'sam');
insert into customer values(11,'adrian');
insert into customer values(12,'mark');
insert into customer values(13,'jim');
insert into customer values(14,'tom');

I want to select customer id(ie; cust_id), customer name(cust_name) and location(from cinema table) of all customer who have not booked in paris.

我想选择所有未在巴黎预订的客户的客户 ID（即 cust_id）、客户名称（cust_name）和位置（来自电影表）。

what i want is --

我想要的是——

cust_id cust_name location
10      sam       New York   
11      adrian    London
14      tom       London

I tried a lot.... one of my code is ---

我尝试了很多......我的代码之一是---

SELECT customer.cust_id,customer.cust_name,
cinema.location as Location FROM booking,cinema,customer
WHERE booking.c_id=cinema.c_id AND location!='Paris';

it gives me 15 result.. I cant think how to do this.. please help me with this.

它给了我 15 个结果..我想不出怎么做..请帮我解决这个问题。

回答by Ben

In your code you're not joining bookingto customer, which is causing your problem. I use explicit joins as opposed to implicit here. Though there is no difference, the explicit syntax is standard.

在代码中你没有加入booking到customer，这是造成你的问题。我在这里使用显式连接而不是隐式连接。尽管没有区别，但显式语法是标准的。

select cu.cust_id, cu.cust_name, ci.location
  from cinema ci
  join booking b
    on ci.c_id = b.c_id
  join customer cu
    on b.cust_id = cu.cust_id
 where ci.location <> 'Paris'

I'm not entirely certain about the structure of your booking table. I would expect a few more columns, for instance number of tickets, etc.

我不完全确定您的预订表的结构。我希望有更多的列，例如票数等。

回答by Ray

Your WHERE statementis not as specific as you want. You need a condition matching booking.cust_id to customer.cust_id:

你WHERE statement的没有你想要的那么具体。你需要一个条件匹配booking.cust_id to customer.cust_id：

WHERE booking.c_id = cinema.c_id 
AND booking.cust_id = customer.cust_id
AND location != 'Paris'

The way you are doing it now, you are geting results for all combinations of customers.

按照您现在的做法，您将获得所有客户组合的结果。

回答by Gaurav Gupta

See the below example :

请参阅以下示例：

Select   
businessuser.UserName,
businessuser.EmailAddress,
businessimages.ImgName, 
featured_cart.FeaturedPlan,featured_cart.StartDate
From featured_cart
Inner Join businessimages on featured_cart.FeaturedProId = businessimages.IdBusinessImages 
Inner Join businessuser  on businessimages.UserId = businessuser.IdBusinessUser
and featured_cart.FeaturedType = "Featured Email"

3 tables are :

3张表是：

1. businessuser
2. businessimages
3. featured_cart

1. 企业用户
2. 企业形象
3. 精选购物车

this example work properly...

这个例子工作正常......

回答by user2904075

MS SQL Server 2008:

微软 SQL Server 2008：

select cust_id, cust_name, location
from customer c 
inner join booking b 
inner join location l
on c.cust_id = b.cust_id and b.c_id=l.c_id

回答by MohanRaj.K.E

Compound Join Joining Four Tables:

复合连接连接四表：

Used 4 Tables

用了4张桌子

1. Customers
2. Orders
3. OrderDetails
4. Products

1. 顾客
2. 订单
3. 订单详细信息
4. 产品

This Example works 100% You can try this in W3schools - Inner Join 'Try yourself' SQL Editor tables are taken from the W3schools Editor.

此示例 100% 有效您可以在 W3schools 中尝试此操作 - 内部联接“尝试自己”SQL 编辑器表取自 W3schools 编辑器。

QUERY:

询问：

Select Customers.CustomerName as Table1_Customer, Orders.EmployeeID as Table2_Employee, OrderDetails.Quantity as Table3_OrderDetails,Products.ProductName as Table4_Products 
From Customers
INNER JOIN Orders 
ON Customers.CustomerID = Orders.CustomerID
INNER JOIN OrderDetails 
ON Orders.OrderID = OrderDetails.OrderID 
INNER JOIN Products
ON OrderDetails.ProductID = Products.ProductID Order by EmployeeID;

回答by Vimal Bhardwaj

hii you can use join like this:

嗨，您可以像这样使用 join：

SQL

SQL

select customer.cust_id,customer.cust_name,cinema.location from cinema,customer,booking where cinema.c_id=booking.c_id and booking.cust_id=customer.cust_id and cinema.location not like 'paris';