LEFT JOIN (SELECT) - Syntax Error In From Clause

[robprestwood]

Can some one point out to me the error in this sql statement?

SELECT CommTypes.Description
FROM CommTypes LEFT JOIN 
   (SELECT * 
    FROM IntroducerBasis 
    WHERE IntroducerBasis.IntroducerCode ='AG'
         AND IntroducerBasis.BasisNumber=1) 
       AS Intro
    ON CommTypes.ID = Intro.CommTypeID;

The error highlights the subsequent SELECT statement as the source of the error.

Thanks

[zmbd]

When I recreate what I can based upon your post. This query appears to work; however, I also re-formatted your query so it could be a typo in the SQL. SPACING is IMPORTAINT!

If correcting the spacing in your SQL doesn't help...
Please list your table names, fields, and relationships.
the "*" in the second select masks the information we need.
I suspect there is a typo or missing relationship.

Also we need any error messages, EXACTLY as they appear, including title, and number.

Please use a format like:
tbl_name1
[fieldname_11] Primary Key, autonumber
[fieldname_12] numeric, long; Foriegn Key, 1:M tbl_name2
etc...

tbl_name2
[fieldname_21] Primary Key, autonumber
[fieldname_22] numeric, long; Foriegn Key, 1:M tbl_name3
etc...

[redz]

try this...

select * 
from
   (SELECT * 
    FROM IntroducerBasis 
    WHERE IntroducerBasis.IntroducerCode ='AG' 
        AND IntroducerBasis.BasisNumber=1) 
      as a
    left join
        (select * 
         from  CommTypes) 
       as b 
    on a.commtypeid = b.id 
 

[zmbd]

The Query in OP runs fine in my test DB.
Once again I suspect the original SQL has a spacing issue, I inadvertently corrected any such problem when I stepped the SQL. I normally try very hard not to modify anything of that nature; however, I'm human.

REDZ's SQL also works just fine; however a slight modification to REDZ post:
change line 1 from Select *
to
line 1 = SELECT b.Description

This will then return only the CommTypes.[Description] value as in the original post.

It is also best practice end the SQL with a Semi-colon
So change line 12 from on a.commtypeid = b.id
toon a.commtypeid = b.id;

Additionally,
Redz's SQL is essentially the same as OP:
In OP the join is from the CommTypes table to the IntroducerBasis table
Whereas
In the redz sql the join is from the IntroducerBasis table to the CommTypes table

in either case the join conditions appear to be the same and on the same field.

[redz]

Hi zmbd
thanks for editing my query