Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server 2008 joining two queries

I'm overlooking something trivial here. This query isn't for any reason in particular other than trying to practice joining two queries. The errors that I get are

Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'inner'.
Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'as'.

And the query is

select t.countyName, x.countyName
    from
    (

    SELECT DISTINCT Patient.patientid, tblStateCounties.countyName, Patient.countyCode
    FROM         Patient INNER JOIN
                          tblStateCounties ON Patient.stateCode = tblStateCounties.stateCode AND Patient.countyCode = tblStateCounties.countyCode
    WHERE     (Patient.patientage > 80)
    ) 
    inner join 
    (
    SELECT DISTINCT Patient.patientid, tblStateCounties.countyName, Patient.countyCode
    FROM         Patient INNER JOIN
                          tblStateCounties ON Patient.stateCode = tblStateCounties.stateCode AND Patient.countyCode = tblStateCounties.countyCode
    WHERE     (Patient.patientage < 80)
    ) as x on t.countyname=x.countyname
like image 251
wootscootinboogie Avatar asked Jan 16 '23 02:01

wootscootinboogie


2 Answers

you forgot use alias in the first subquery.

like image 56
Conan Avatar answered Jan 25 '23 23:01

Conan


select t.countyName, x.countyName
from
(

     SELECT DISTINCT Patient.patientid, tblStateCounties.countyName, Patient.countyCode
     FROM         Patient 
     INNER JOIN tblStateCounties ON Patient.stateCode = tblStateCounties.stateCode 
                                 AND Patient.countyCode = tblStateCounties.countyCode
      WHERE     (Patient.patientage > 80)
) rsT
inner join 
(
      SELECT DISTINCT Patient.patientid, tblStateCounties.countyName, Patient.countyCode
      FROM         Patient 
      INNER JOIN tblStateCounties ON Patient.stateCode = tblStateCounties.stateCode   
                                 AND Patient.countyCode = tblStateCounties.countyCode
      WHERE     (Patient.patientage < 80)
) rsX on rsT.countyname=rsX.countyname
like image 31
Bert Avatar answered Jan 25 '23 22:01

Bert