CODEWITHSUNDEEP
sql
lpause
lpause

Reputation: 3

SQL Using two tables for a query

I'm learning SQL for a while and I'm stuck with this problem.

I have two tables for a storage company. In one table for the bookings for the whole company consisting booking no., date, product nr., quantity and storage room.

And in one table I have storage facility name and storage rooms to define the room belong to which facility

I want to have the sum of the quantity of one specific product in one specific storage facility

SELECT sum(Quantity) from Bookings where ProductNo=8888 and Storage.StorageFacility="Central"

Do I have to use a more complex query for the solution?

Upvotes: 0

Views: 50

Answers (2)

Nivas
Nivas

Reputation: 18344

Your storage facility is in a different table than the BOOKINGS table (and they are related via STORAGE_ROOM). So you need to join these tables:

select sum(Quantity)
  from bookings b 
  join storage_facility_details sfd 
    on b.storage_room = sfd.storage_room
 where sfd.storage_facility = 'x'
   and bookings.productno='8888'

Upvotes: 3

alexkelbo
alexkelbo

Reputation: 724

The syntax of SQL can be slightly different on different database systems. Usually quotes (") are used to refer to tables and similar objects. Use single quotes (') to give values. Also, all the tables that you refer columns of need to appear after FROM. If the columns quantity and storagefacility appear in both tables you need to fully quality the name.

Also you need to join the two tables on a column, so the productno must also be present in your storage table.

SELECT sum(quantity) FROM bookings, storage
   WHERE (bookings.productno = storage.productno)
         AND
         (bookings.productno='8888' AND storage.storagefacility='Central');

Upvotes: 1

Related Questions