|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
 |
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
|
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
Dev Articles Community Forums Sponsor:
|
|
|
|
#1
March 17th, 2004, 06:01 PM
|
|||
|
|||
Help with mulit-join SQL
Hello,
I know there must be a SQL statement (using MS SQL server 2000) that could be created without having to create a VB program to do the same thing (which I have already started, since I suck at writing complex SQL). I tried doing this in Crystal Reports 8 but found that too frustrating. Any help is appreciated, anyways here are the three tables and fields in question: INVMAST.................... INVLOC................. INVMSPEC itemkey (pk)------> itemkey* (pk) --> itemkey* (pk) specgrav............... location (pk) .......propnum (pk) ............................stdcost ..............propval *indexed with duplicates, hence are part of a combined primary key in their respective table. Required report (sorted by itemkey): itemkey stdcost1 stdcost2 stdcost3 calcval1 calcval2 calcval3 calcval4 specgrav Expressions: stdcost1 = (stdcost WHERE location = '1') stdcost2 = (stdcost WHERE location = '2') stdcost3 = (stdcost WHERE location = '3') calcval1 = (1-(propval WHERE propnum = 4))*100 calcval2 = (1-(propval WHERE propnum = 3))*100 calcval3 = (propval WHERE propnum = 6) * 100 calcval4 = (propval WHERE propnum = 5) * 100 Example INVMAST: itemkey specgrav .................AB1...... 0.6 .................AB2...... 0.5 INVLOC: itemkey location stdcost .............. AB1 ..... 1 .... 4.00 .............. AB1 ..... 2 .... 4.25 .............. AB1 ..... 3 .... 4.50 .............. AB2 ..... 1 .... 3.50 .............. AB2 ..... 2 .... 3.75 .............. AB2 ..... 3 .... 4.00 INVMSPEC: itemkey propnum propval .................. AB1 ..... 3 ...... 0.2 .................. AB1 ..... 4 ...... 0.22 .................. AB1 ..... 5 ...... 0.25 .................. AB1 ..... 6 ...... 0.3 .................. AB2 ..... 3 ...... 0.1 .................. AB2 ..... 4 ...... 0.15 .................. AB2 ..... 5 ...... 0.21 .................. AB2 ..... 6 ...... 0.35 with final report looking like itemkey stdcost1 stdcost2 stdcost3 calcval1 calcval2 calcval3 calcval4 specgrav AB1 ....... 4.00 ..... 4.25 ..... 4.50 ..... 78.0 .... 80.0 ..... 30.0 .... 25.0 ... 0.6 AB2 ....... 3.50 ..... 3.75 ..... 4.00 ..... 85.0 .... 90.0 ..... 35.0 .... 21.0 ... 0.5 Clear as mud?  Anyways easily done using VB, but just thought it would be so much quicker with just using a SQL statement. Excuse the dots, but spacing just does not want to work properly :P Last edited by Slow2Code : March 17th, 2004 at 06:16 PM. Reason: spacing blah!! 3rd try
|
|
#2
March 17th, 2004, 09:37 PM
|
||||
|
||||
|
Please read the forum rules first. Code should be placed in code tags. It is not advisable to post your entire message using BOLD either.
|
|
| Viewing: Dev Articles Community Forums > Databases > Microsoft SQL Server > Help with mulit-join SQL |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
