joining two select - 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

joining two select

Author

Topic

THM
Starting Member

4 Posts

Posted - 2011-11-02 : 10:54:53

The script below runs and produces two list of values, both of which share the same holeid, i would like to join them to make one list (joined on holeid), any help will be great!

Thank

(
SELECT SAMPLE.HOLEID,AVG(cast(VALUE as FLOAT)) AS FRAG_DOL_AVG
FROM SAMPLE AS SAMPLE
LEFT JOIN SAMPLEDETAILS
ON SAMPLE.SAMPLEID=SAMPLEDETAILS.SAMPLEID

WHERE SAMPLE.PROJECTCODE = 'UG'
AND SAMPLEDETAILS.NAME = 'FRAG_DOL'
AND SAMPLE.SAMPFROM BETWEEN '{ctrl::DV_FROM}' AND '{ctrl::DV_TO}'

GROUP BY SAMPLE.HOLEID
)

(
SELECT SAMPLE.HOLEID,AVG(cast(VALUE as FLOAT)) AS FRAG_TC_AVG
FROM SAMPLE
LEFT JOIN SAMPLEDETAILS
ON SAMPLE.SAMPLEID=SAMPLEDETAILS.SAMPLEID

WHERE SAMPLE.PROJECTCODE = 'UG'
AND SAMPLEDETAILS.NAME = 'FRAG_TC'
AND SAMPLE.SAMPFROM BETWEEN '{ctrl::DV_FROM}' AND '{ctrl::DV_TO}'

GROUP BY SAMPLE.HOLEID

)

ORDER BY SAMPLE.HOLEID

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-02 : 10:57:45

select * from
(
SELECT SAMPLE.HOLEID,AVG(cast(VALUE as FLOAT)) AS FRAG_DOL_AVG
FROM SAMPLE AS SAMPLE
LEFT JOIN SAMPLEDETAILS
ON SAMPLE.SAMPLEID=SAMPLEDETAILS.SAMPLEID

WHERE SAMPLE.PROJECTCODE = 'UG'
AND SAMPLEDETAILS.NAME = 'FRAG_DOL'
AND SAMPLE.SAMPFROM BETWEEN '{ctrl::DV_FROM}' AND '{ctrl::DV_TO}'

GROUP BY SAMPLE.HOLEID
) a
join
(
SELECT SAMPLE.HOLEID,AVG(cast(VALUE as FLOAT)) AS FRAG_TC_AVG
FROM SAMPLE
LEFT JOIN SAMPLEDETAILS
ON SAMPLE.SAMPLEID=SAMPLEDETAILS.SAMPLEID

WHERE SAMPLE.PROJECTCODE = 'UG'
AND SAMPLEDETAILS.NAME = 'FRAG_TC'
AND SAMPLE.SAMPFROM BETWEEN '{ctrl::DV_FROM}' AND '{ctrl::DV_TO}'
GROUP BY SAMPLE.HOLEID
) b
on a.HOLEID = b.HOLEID

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-02 : 11:00:27

you dont need two queries. just below is enough


SELECT SAMPLE.HOLEID,AVG(CASE WHEN SAMPLEDETAILS.NAME = 'FRAG_DOL' THEN cast(VALUE as FLOAT) ELSE NULL END) AS FRAG_DOL_AVG,
AVG(CASE WHEN SAMPLEDETAILS.NAME = 'FRAG_TC' THEN cast(VALUE as FLOAT) ELSE NULL END) AS FRAG_TC_AVG
FROM SAMPLE AS SAMPLE
LEFT JOIN SAMPLEDETAILS
ON SAMPLE.SAMPLEID=SAMPLEDETAILS.SAMPLEID

WHERE SAMPLE.PROJECTCODE = 'UG'
AND SAMPLE.SAMPFROM BETWEEN '{ctrl::DV_FROM}' AND '{ctrl::DV_TO}' 

GROUP BY SAMPLE.HOLEID

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

THM
Starting Member

4 Posts

Posted - 2011-11-02 : 11:06:52

They both work great! thanks guys!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-02 : 11:11:48

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources