mysql - select based on more than 3 tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

mysql - select based on more than 3 tables

Author

Topic

nego78
Starting Member

5 Posts

Posted - 2004-06-28 : 15:22:44

Hi

i'm going to build a shop/store site where client would like to have history of sale
i think there'll be almost 3 tables

table1 - about product
idp,product name, desc, etc

table2 - about current stae of quantity where client will be inserting new quantities of products

idq,idp,buyprice,saleprice,insertdate,quantity

table3 - in this table i'd like to put all sale

ids,idq,idclient,quantity

it's a scheme so forgive me:)

how do i show all product from table 2 and subtract all sold product from table3 ?

is this is possible without use of subqueries ?

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-06-28 : 15:32:06

schoolwork?

nego78
Starting Member

5 Posts

Posted - 2004-06-28 : 15:43:51

no

my idea to create very configurable shop

i suppose that i can do it with subqueries but i'll work with mysql 4.0.x which doesn't support itd, so i'm looking for a another conceptption

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-28 : 15:47:41

This is a SQL Server site. You might try dbforums.com as they have a mySql forum there.

Tara

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-06-28 : 17:39:55

quote:
Originally posted by tduggan

This is a SQL Server site. You might try dbforums.com as they have a mySql forum there.

no doubt they can help you at dbforums. However, this seems like a generic SQL question to me. I take it that mysql doesn't support a subquery, is that correct?

Anyway, here goes:

-- drop table table1
-- drop table table2
-- drop table table3

--tables
create table table1 (idp int, product_name varchar(20), [description] varchar(100))
create table table2 (idq int, idp int, buyprice money, saleprice money, insertdate datetime, quantity int)
create table table3 (ids int, idq int, idclient varchar(20), quantity int)

--truncate table table1
--truncate table table2
--truncate table table3

--test data
insert table1 (idp, product_name) values (1, 'foo')
insert table1 (idp, product_name) values (2, 'bar')
insert table1 (idp, product_name) values (3, 'foobar')
insert table2 (idq, idp, buyprice, saleprice, insertdate, quantity) values (1, 1, 50, 40, getdate(), 5)
insert table2 (idq, idp, buyprice, saleprice, insertdate, quantity) values (2, 2, 75, 70, getdate(), 10)
insert table2 (idq, idp, buyprice, saleprice, insertdate, quantity) values (3, 3, 25, 20, getdate(), 15)
insert table3 (ids, idq, idclient, quantity) values (1, 1, 101, 2)
insert table3 (ids, idq, idclient, quantity) values (2, 2, 102, 6)
insert table3 (ids, idq, idclient, quantity) values (3, 3, 103, 10)

--select * from table1
--select * from table2
--select * from table3

Here is the select that will do what you ask:

SELECT	a.product_name, (b.quantity - c.quantity) as QUANTITY
FROM	table1 a INNER JOIN table2 b
	 ON a.idp = b.idp 
	INNER JOIN table3 c
	 ON b.idq = c.idq

product_name         QUANTITY    
-------------------- ----------- 
foo                  3
bar                  4
foobar               5

(3 row(s) affected)

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources