[kevkgho]

I am new to SQL and I wonder if this can be done.

I have 3 tables:

Table "SITES"
--------------------------------------
SITEID
--------------------------------------
S1
S2

Table "BALANCE"
--------------------------------------
PRODUCTID SITEID QTY
--------------------------------------
Apple S1 120
Apple S2 50
Egg S1 60
Egg S2 0

Table "PRODUCTS"
--------------------------------------
PRODUCTID
--------------------------------------
Apple
Egg

How do I put together a query and generate a recordset like:
--------------------------------------
PRODUCTID S1 S2
--------------------------------------
APPLE 120 50
Egg 60 0

I am new to SQL, please enlight me. ray:

Million thanks in advance. Peace.

Kevin

 

[Ptroy]

This is What I think it might be like for SQL Server, didn't catch what DB you're using.

SELECT PRODUCTS.PRODUCTID, SITES.SITEID, BALANCE.QTY
INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTID = BALANCE.PRODUCTID
INNER JOIN SITES ON SITES.SITEID = BALANCE.SITEID
WHERE (SITES.SITEID = 's1' OR SITES.SITEID = 's2')
AND (PRODUCTS.PRODUCTID = 'APPLE' OR PRODUCTS.PRODUCTID = 'EGG')

Anyways, just a suggestion, I am no expert by any means. I'm just trying to pass along what I've learned, as I am graduating in a week.

Let me know if it's ORACLE though,
I did do ORACLE SQL this past summer for a company, manipulating production data for reports. I was surprised though, the Biz analyst was clueless with working with DB's so I think that is why they hired me. Anyways, I get done with that internship, then my focus is back to all programming (VB and Java) for school, now this semester its switching back to T-SQL for SQL Server, which most of my assignments this year, used.
Well sorry for the jibberish, let me know what the above gives you back for errors.

 

[Jim_from_Oz]

There is a slight error in this query because the table BALANCE is not nominated. I think this should work:

SELECT PRODUCTS.PRODUCTID, SITES.SITEID, BALANCE.QTY FROM PRODUCTS
INNER JOIN BALANCE ON PRODUCTS.PRODUCTID = BALANCE.PRODUCTID
INNER JOIN SITES ON SITES.SITEID = BALANCE.SITEID
WHERE (SITES.SITEID = 's1' OR SITES.SITEID = 's2')
AND (PRODUCTS.PRODUCTID = 'APPLE' OR PRODUCTS.PRODUCTID = 'EGG')