SQL multi table query - is this possible ?

kevkgho · 05-04-2009, 12:45 AM

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.

Million thanks in advance. Peace.

Kevin

Ptroy · 05-06-2009, 11:41 PM

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 · 05-08-2009, 04:08 PM

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')

05-04-2009, 12:45 AM	#1 (permalink)
kevkgho Registered User Join Date: May 2009 Posts: 1 OS: vista	SQL multi table query - is this possible ? 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. Million thanks in advance. Peace. Kevin

05-06-2009, 11:41 PM	#2 (permalink)
Ptroy Registered User Join Date: Aug 2005 Location: Wisconsin Posts: 62 OS: Vista Biz(x86), Server 2003 Enterprise(x86), XP Pro(x86), XP Home(x86)	Re: SQL multi table query - is this possible ? 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.

05-08-2009, 04:08 PM	#3 (permalink)
Jim_from_Oz Registered User Join Date: Jul 2007 Location: Born UK, Worked in Libya, Kenya, Uganda, Settled in Australia. Now resident outside Melbourne, Victoria Posts: 19 OS: Windows XP SP2	Re: SQL multi table query - is this possible ? 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')