queriefromtwotables - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

queriefromtwotables

Author

Topic

huusker
Starting Member

3 Posts

Posted - 2010-10-27 : 14:58:52

Hi,
I want one or more queries to print a lay-out
for total hired parts per relation
My difficulty is more relations in tabel1 and more parts in tabel2
See below
Can someone help me with this sql-code?

In dutch:
tabel1
GEHUURDHOOFD
huurbonnr relatie
________ _____
gehdid1 rid10
gehdid2 rid15
gehdid3 rid10
gehdid4 rid18

tabel2
GEHUURDRIJEN
huurbonnr huurbonrijnr nr artcode aantal
________ _________ __ ______ ______
gehdid1 gehdregid1 nr0 artcode1 aantal5
gehdid1 gehdregid2 nr1 artcode3 aantal1
gehdid1 gehdregid3 nr2 artcode2 aantal12
gehdid2 gehdregid4 nr0 artcode2 aantal19
gehdid3 gehdregid5 nr0 artcode8 aantal21
gehdid3 gehdregid6 nr1 artcode3 aantal1
gehdid4 gehdregid7 nr0 artcode6 aantal6
gehdid4 gehdregid8 nr1 artcode9 aantal2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-27 : 15:24:22

so wat should be your desired output in above case?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

huusker
Starting Member

3 Posts

Posted - 2010-10-27 : 15:39:45

Relation: rid10
My desired output would be:
articlecode totalhired
artcode1 5
artcode2 12
artcode3 2
artcode8 21

Relation rid15
articlecode total hired
artcode2 19

Relation rid18
articlecode total
artcode6 6
artcode9 2

huusker
Starting Member

3 Posts

Posted - 2010-10-28 : 01:40:20

I've tried this sql-code:

select table1.rid, table2.gehdregid, table2.artcode, table2.number-hired(in dutch'aantal')
from table1
inner join table2
on table1.gehdid = table2.gehdid

the result of this is, I think
rid10 gehdregid1 nr0 artcode1 aantal 5
rid10 gehdregid2 nr1 artcode3 aantal 1
rid10 gehdregid3 nr2 artcode2 aantal 12
rid15 gehdregid4 nr0 artcode2 aantal 19
rid10 gehdregid5 nr0 artcode8 aantal 21
rid10 gehdregid6 nr1 artcode3 aantal 1
rid18 gehdregid7 nr0 artcode6 aantal 6
rid18 gehdregid8 nr1 artcode9 aantal 2

my question is, how can I produce my desired output from the result of these select?
thank you in advance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources