Combining multiple SQL Queries

Question

I want to make a query to list cats that took longer than average cats to sell? I have five tables:

Animal, Sale, AnimalOrderItem, AnimalOrder, and SaleAnimal

Animal table: AnimalID, Name, Category (cat, dog, fish)

SaleAnimal table: SaleID, AnimalID, SalePrice

Sale table: SaleID, date, employeeID, CustomerID

AnimalOrderItem table: OrderID, AnimalID, cost

AnimalOrder: OrderID, OrderDate, ReceivingDate, SupplierID, ShippingCost, EmployeeID

There is other tables I don’t think they have an effect on the query.

I thought of the following ... make a query to calculate days to sell for all ex.:

[SaleDate]-[ReceiveDate] AS DaysToSell
Have the INNER JOIN built:
Sale INNER JOIN ((AnimalOrder INNER JOIN (Animal INNER JOIN AnimalOrderItem
ON Animal.AnimalID = AnimalOrderItem.AnimalID) ON AnimalOrder.
OrderID = AnimalOrderItem.OrderID) INNER JOIN SaleAnimal ON Animal.
AnimalID = SaleAnimal.AnimalID) ON Sale.SaleID = SaleAnimal.SaleID

Create another query based on the above query

SELECT AnimalID, Name, Category, DaysToSell
WHERE Category="Cat" AND DaysToSell>
(SELECT Avg(DaysToSell)
FROM the earlier query
WHERE Category="Cat"
ORDER BY DaysToSell DESC;

After running the query it I got error saying

ORA-00921: unexpected end of SQL command

Any suggestions! please

If you want some help you should try to format your question. Not just copy and paste. — José Leal
– José Leal, Commented May 15, 2009 at 0:35

Andomar · Accepted Answer · 2009-05-15 08:01:13Z

1

Queries can be combined with a subquery. For example,

select *
from (
    select * 
    from mytable
) subquery

Applying this pattern to your problem seems fairly straightforward.

answered May 15, 2009 at 8:01

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user98832 Over a year ago

you mean making it like Nebakanezer suggestion, can you explain with an example please

Srikar Doddi · Accepted Answer · 2009-05-15 00:35:35Z

0

I don't see the closed bracket that matches with the select avg

answered May 15, 2009 at 0:35

Srikar Doddi

15.6k15 gold badges70 silver badges107 bronze badges

1 Comment

user98832 Over a year ago

thanks for that ... but I really need the help of connecting the two queries together and make it work

Mark Sherretta · Accepted Answer · 2009-05-15 01:29:59Z

0

Ok, I've come up with this:

    SELECT AnimalID, Name, Category,
           [SaleDate]-[ReceiveDate] AS DaysToSell
    FROM   Sale INNER JOIN ((AnimalOrder INNER JOIN (Animal INNER JOIN AnimalOrderItem ON Animal.AnimalID = AnimalOrderItem.AnimalID) ON AnimalOrder.OrderID = AnimalOrderItem.OrderID)
           INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID) ON Sale.SaleID = SaleAnimal.SaleID
    WHERE  Category = "Cat"
    AND    ([SaleDate]-[ReceiveDate]) > (SELECT AVG([SaleDate]-[ReceiveDate])
                                         FROM   Sale INNER JOIN ((AnimalOrder INNER JOIN (Animal INNER JOIN AnimalOrderItem ON Animal.AnimalID = AnimalOrderItem.AnimalID) ON AnimalOrder.OrderID = AnimalOrderItem.OrderID)
                                         INNER JOIN SaleAnimal ON Animal.AnimalID =SaleAnimal.AnimalID) ON Sale.SaleID = SaleAnimal.SaleID
                                         WHERE Category = "Cat")
    ORDER BY ([SaleDate]-[ReceiveDate]) DESC;

edited May 15, 2009 at 1:29

answered May 15, 2009 at 0:37

Mark Sherretta

10.3k4 gold badges40 silver badges43 bronze badges

1 Comment

user98832 Over a year ago

I got missing expression error .. I really appreciate your help guy's

Collectives™ on Stack Overflow

Combining multiple SQL Queries

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related