5

I have a very old Access application that uses SQL Pass-Through Queries. This application has been nursed along through multiple server upgrades, and we recently moved up to SQL 2012.

I now get a syntax error. The last two lines of the code are where the problem is. I believe it is some kind of JOIN, but I've never seen this syntax. Removing the "*" allows the query to run, but leaves out data. Is there a easy replacement of this syntax or do I need to redesign these queries using the JOIN phrasing?

SELECT survey.hole,survey_date,pit,cu,rec,zn, mn,north,east,elevation,
       rtrim(min_type) min_type,rtrim(rock_type) rock_type,
       rtrim(alt_type) alt_type, rtrim(min_style) min_style
FROM survey,assay,geology 
WHERE  mined_out IS NULL
AND pit = (SELECT fmpit FROM tyrctl WHERE source = 'export by date')
AND  (survey_date >= (SELECT fmdate FROM tyrctl WHERE source = 'export by date') 
AND survey_date <= (SELECT todate FROM tyrctl WHERE source = 'export by date')) 
AND survey.hole *= assay.hole
AND survey.hole *= geology.hole
1
  • 1
    That is the "old-style" JOIN syntax.
    – Hannah Vernon
    Nov 12, 2015 at 0:15

1 Answer 1

6

Your query is using the pre-ANSI (and possibly original) JOIN syntax. Back in "the day", joining tables was done by specifying all of the tables, separated by commas, in the FROM clause, and then specifying the join condition(s) in the WHERE clause. Equating the current ANSI style with the old style gives you the following chart of options:

  • [INNER] JOIN: TableA.Field = TableB.Field
  • LEFT [OUTER] JOIN: TableA.Field *= TableB.Field
  • RIGHT [OUTER] JOIN: TableA.Field =* TableB.Field
  • FULL [OUTER] JOIN: TableA.Field *=* TableB.Field
  • CROSS JOIN: {nothing specified to relate the two tables}

The pre-ANSI syntax is / was supported (sometimes with slight variation) by the various RDBMSes. However, it has been deprecated for a while in Microsoft SQL Server, and was officially removed in SQL Server 2012 (as you are now seeing). This was noted in the Discontinued Database Engine Functionality in SQL Server 2012 MSDN page (2nd to last row in the grid).

In your query, you have 3 tables:

FROM survey,assay,geology

and two LEFT JOIN conditions:

AND survey.hole *= assay.hole
AND survey.hole *= geology.hole

But yes, you can rewrite it as follows:

SELECT sy.hole, survey_date, pit, cu, rec, zn, mn, north, east, elevation,
       rtrim(min_type) AS [min_type], rtrim(rock_type) AS [rock_type],
       rtrim(alt_type) AS [alt_type], rtrim(min_style) AS [min_style]
FROM survey sy
LEFT JOIN assay ay
       ON ay.hole = sy.hole
LEFT JOIN geology gy
       ON gy.hole = sy.hole
WHERE  mined_out IS NULL
AND pit = (SELECT fmpit FROM tyrctl WHERE source = 'export by date')
AND (
      survey_date >= (SELECT fmdate FROM tyrctl WHERE source = 'export by date')
  AND survey_date <= (SELECT todate FROM tyrctl WHERE source = 'export by date')
    )

Technically, you don't need the parenthesis around the two survey_date conditions in the WHERE clause as they are all AND conditions, but if you prefer it for readability then it is fine.


As @ypercube pointed out in a comment on this answer, the query can be further simplified to since the 3 subqueries in the WHERE clause all reference the same row of the same table. So that could look at follows:

SELECT sy.hole, survey_date, pit, cu, rec, zn, mn, north, east, elevation,
       rtrim(min_type) AS [min_type], rtrim(rock_type) AS [rock_type],
       rtrim(alt_type) AS [alt_type], rtrim(min_style) AS [min_style]
FROM survey sy
INNER JOIN tyrctl tl
        ON sy.survey_date >= tl.fmdate
       AND sy.survey_date <= tl.todate
       AND sy.pit = tl.fmpit
       AND tl.source = 'export by date'
LEFT JOIN assay ay
       ON ay.hole = sy.hole
LEFT JOIN geology gy
       ON gy.hole = sy.hole
WHERE  mined_out IS NULL;
4
  • 2
    Was there really an *=* operator? I thought only *= and =* were there. Nov 12, 2015 at 8:20
  • 2
    It gives me an Incorrect syntax near '*' error when I try a query with *=* in SQL Server 2008 R2. (Works fine with the other two.) I tried it in a database with a matching compatibility level (80), of course.
    – Andriy M
    Nov 12, 2015 at 8:27
  • 2
    The 3 subqueries have identical conditions so they could be replaced by a single correlated one: AND EXISTS (SELECT * FROM tyrctl WHERE source = 'export by date' AND x.pit = fmpit AND y.survey_date >= fmdate AND y.survey_date <= todate) - where x and y are the correct aliases (sy, ay or gy) Nov 12, 2015 at 18:27
  • 2
    @ypercube regarding the *=* syntax, I could be misremembering. I originally learned this stuff on DB2/MVS back in mid '96. Between that and there being vendor-specific variations, maybe it was never in SQL Server. Maybe it was never anywhere and I dreamt it up ;-). I will cross it off for now. And good call on reducing the AND conditions. That could just as well be a simple INNER JOIN since it only matches one row in tyrctl. Also, I assume those fields are in the main survey table (sy) else the rows coming back wouldn't be reduced. Nov 12, 2015 at 18:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.