# Question in query optimization

Page 1 of 1

## 2 Replies - 862 Views - Last Post: 09 April 2011 - 08:06 PMRate Topic: //<![CDATA[ rating = new ipb.rating( 'topic_rate_', { url: 'http://www.dreamincode.net/forums/index.php?app=forums&module=ajax&section=topics&do=rateTopic&t=226870&amp;s=50dab6bfe62a567e63851daf5603bf80&md5check=' + ipb.vars['secure_hash'], cur_rating: 0, rated: 0, allow_rate: 0, multi_rate: 1, show_rate_text: true } ); //]]>

### #1 mon mon

Reputation: -1
• Posts: 128
• Joined: 31-December 09

# Question in query optimization

Posted 09 April 2011 - 06:20 PM

Quetion is :
the schema:
Product (ProductId, ProductName, Unit price,InStockQuantity, SupplierId)
Supplier(SupplierId, SupplierName, Country, City, Contact#)
OrderDetail(ProductId,OrderId, Quantity,TotalPrice)
Order(OrderID, OrderDate, RequiredDate)
/////////////////////////////////////////////////////////

```Select Product.ProductName, OrderDetail. Quantity
From product, supplier, OrderDetail , order
Where (OrderDetail. Quantity> 400
Or ((Supplier.City = 'Berlin' And order.OrderDate > ‘26- 3-2007’) Or OrderDetail. Quantity<= 400))
Or( Product.SupplierId = Supplier.SupplierId
And OrderDetail.ProductId =  Product.ProductId
And OrderDetail.OrderID =  Order. OrderID)

```

/////////////////////////////////////////////// "Question"
If you know that Product table has 100 tuples, Order table has 200 tuples, OrderDetail table has 300 tuples, Supplier table has 450 tuples, and no tuples satisfy the condition OrderDetail.ProductId = Product.ProductId. how many tuples will returned ?
////////////////////////////////////////////////////////////////////////////////
#my solution is :(will not return any thing ) because no tuples satisfy the condition OrderDetail.ProductId = Product.ProductId and there are projection for (Product.ProductName,OrderDetail.Quantity)

Is it correct?

Is This A Good Question/Topic? 0

## Replies To: Question in query optimization

### #2 macosxnerd101

• Games, Graphs, and Auctions

Reputation: 12241
• Posts: 45,327
• Joined: 27-December 08

## Re: Question in query optimization

Posted 09 April 2011 - 06:22 PM

Moved to Databases.

That is correct.

### #3 baavgai

• Dreaming Coder

Reputation: 7106
• Posts: 14,823
• Joined: 16-October 07

## Re: Question in query optimization

Posted 09 April 2011 - 08:06 PM

Tuples? What are tuples? If this is a SQL RDBMS, then there are Tables with Rows.

Your query has issues, since it simply doesn't join properly. Let's format it a little:
```Select Product.ProductName, OrderDetail. Quantity
From product, supplier, OrderDetail , order
Where
(OrderDetail. Quantity> 400
Or (
(Supplier.City = 'Berlin' And order.OrderDate > '26- 3-2007')
Or OrderDetail. Quantity<= 400
)
)
Or (
Product.SupplierId = Supplier.SupplierId
And OrderDetail.ProductId =  Product.ProductId
And OrderDetail.OrderID =  Order. OrderID
)

```

So, if "OrderDetail. Quantity> 400" I ignore the joins? I don't know that a decent database would actually let you get away with your logic. If it did, you're looking at some unexpected results.

Written correctly, the basic table joins would look like:
```Select *
From OrderDetail
inner join Product
on OrderDetail.ProductId =  Product.ProductId
inner join Order
on OrderDetail.OrderID =  Order.OrderID
inner join Supplier
on Product.SupplierId = Supplier.SupplierId

```

Now that you have a clean join, you can worry out your wheres.
```(OrderDetail. Quantity> 400
Or (
(Supplier.City = 'Berlin' And order.OrderDate > '26- 3-2007')
Or OrderDetail. Quantity<= 400
)

```

Let's contemplate that for a moment:
```OrderDetail. Quantity> 400
Or OrderDetail. Quantity<= 400

```

You can stop now, you've already asked for everything, the Supplier.City bit is ignored.