Incorrect syntax error in join query sql

Question

I have the following query.

with getstock as 
(
    select 
        a.bomparent, a.bomchild, a.bomqty, a.bompos, a.baltmethod, a.bomissue
    from 
        bom a
    where 
        bomparent = 'QZ10-0262601' and baltmethod = '1'

    union all

    select 
        parent.bomparent, parent.bomchild, parent.bomqty, parent.bompos, parent.baltmethod, parent.bomissue 
    from 
        getstock as a
    inner join 
        bom as parent, stock as s on parent.bomparent = a.bomchild
    where 
        parent.baltmethod = '1' and parent.bomparent = s.stocknum
)
select  *
from getstock

When I run it, I get the following error.

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ','.

Where is the issue?

Community · Accepted Answer · 2020-07-29 18:25:24Z

5

You can not use multiple tables in JOIN. It's possible in FROM, but I won't suggest that. That's the old-style JOIN syntax. You should rewrite your query using explicit JOIN:

with getstock as 
(
    select 
        a.bomparent, a.bomchild, a.bomqty, a.bompos, a.baltmethod, a.bomissue
    from 
        bom a
    where 
        bomparent = 'QZ10-0262601' and baltmethod = '1'

    union all

    select 
        parent.bomparent, parent.bomchild, parent.bomqty, parent.bompos, parent.baltmethod, parent.bomissue 
    from 
        getstock as a
    inner join 
        bom as parent on parent.bomparent = a.bomchild
    inner join 
        stock as s on parent.bomparent = s.stocknum
    where 
        parent.baltmethod = '1'
)
select  *
from getstock

For more reading: Avoid using old-style JOIN syntax.

edited Jul 29, 2020 at 18:25

CommunityBot

11 silver badge

answered Aug 7, 2015 at 4:53

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tharif · Accepted Answer · 2015-08-07 05:04:32Z

1

You can also re-write your query with proper joins for select with union all as below :

SELECT        bomparent, bomchild, bomqty, bompos, baltmethod, bomissue
FROM            bom AS a
WHERE        (bomparent = 'QZ10-0262601') AND (baltmethod = '1')

    union all


SELECT        bom.bomparent, bom.bomchild, bom.bomqty, bom.bompos, bom.baltmethod, bom.bomissue
FROM            getstock INNER JOIN
                         bom ON getstock.bomchild = bom.bomparent INNER JOIN
                         stock ON bom.bomparent = stock.stocknum
WHERE        (bom.baltmethod = N'1')

answered Aug 7, 2015 at 5:04

Tharif

14k10 gold badges57 silver badges78 bronze badges

Comments

david sam · Accepted Answer · 2015-08-07 05:04:32Z

1

with getstock as 
(
    select a.bomparent, a.bomchild, a.bomqty, a.bompos,
    a.baltmethod, a.bomissue
    from bom a
    where bomparent = 'QZ10-0262601' and baltmethod = '1'
    union all
    select parent.bomparent, parent.bomchild, parent.bomqty,
    parent.bompos,  parent.baltmethod, parent.bomissue 
    from getstock as a
    inner join bom as parent
     on parent.bomparent = a.bomchild
    inner join stock as s
     parent.bomparent = s.stocknum
   where parent.baltmethod = '1' 
)
select  *
from getstock

answered Aug 7, 2015 at 5:04

david sam

5312 gold badges8 silver badges25 bronze badges

Comments

John · Accepted Answer · 2015-08-07 05:24:35Z

1

Following code should work but its not following best practices. Always use Join condition properly(INNER JOIN ...ON).

with getstock as 
(
    select a.bomparent, a.bomchild, a.bomqty, a.bompos, a.baltmethod, a.bomissue
    from bom a
    where bomparent = 'QZ10-0262601' and baltmethod = '1'
    union all

    select parent.bomparent, parent.bomchild, parent.bomqty, parent.bompos, parent.baltmethod, parent.bomissue 
    from getstock as a,
        bom as parent, 
        stock as s  
    where parent.bomparent = a.bomchild and
        parent.baltmethod = '1' and parent.bomparent = s.stocknum
)
select  * from getstock

edited Aug 7, 2015 at 5:24

answered Aug 7, 2015 at 5:19

John

112 bronze badges

2 Comments

marc_s Over a year ago

And you're NOT using INNER JOIN in your example ..... this is even worse than the OP's question ...

John Over a year ago

I know, But this is a way of soving the issues. That's all i am trying to convey. I have already mentioned that its not a good way to do in this way.

Collectives™ on Stack Overflow

Incorrect syntax error in join query sql

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related