CODEWITHSUNDEEP
sqlms-accessjoin
Vivek
Vivek

Reputation: 141

Fetching from 3 tables, with one condition on one table

I have 4 tables

Order(OrderId,OrderDate,Firm,ConsumerId)
OrderProductDetails(OrderId,ProductId,Firm,Quantity,Rate,Fulfilled,PendingQuantity) [fulfilled is boolean]
Product(ProductId,ProductName)
Consumer(ConsumerId,ConsumerName)

I want to fetch those orders (OrderId,OrderDate,ConsumerName) that are not completely fulfilled. I tried a lot, but wasn't able to make it work. The query seems simple, but I do not know how complex it is gonna be.

Upvotes: 0

Views: 69

Answers (2)

Hardik Bhangale
Hardik Bhangale

Reputation: 18

The following query should work fine.
Notes:
1) I have renamed Order table to OrderT,
2)Renamed Consumer -> ConsumerT

SELECT DISTINCT OrderProductDetails.OrderId, OrderT.OrderDate, ConsumerT.ConsumerName FROM OrderProductDetails, ConsumerT INNER JOIN OrderT ON ConsumerT.ConsumerId = OrderT.ConsumerId WHERE (([OrderProductDetails].[Fulfilled]=0) AND ([OrderProductDetails].[OrderId]=[OrderT].[OrderId]));

The Logic behind the query is : You want the data mainly from two tables OrderT and ConsumerT. But all data depends upon OrderProductDetails.Fulfilled and last but one is the orders which are not fulfilled only those consumer name is required. MS Access Design view while creating query helped a lot.

Upvotes: 0

No'am Newman
No'am Newman

Reputation: 6477

From the information that you have given, the following should work.

select order.orderid, order.orderdate, consumers.consumername
from order
inner join orderproductdetails on orderproductdetails.orderid = order.orderid
inner join consumers on consumers.consumerid = order.consumerid
where orderproductdetails.fulfilled = 0

I am assuming that you have a 'consumers' table, and that boolean 'false' is represented by the value 0.

Upvotes: 1

Related Questions