Question Re: Join on multiple tables not working in Technology Q&A

Join on multiple tables not working

t1950 — Wed, 26 Oct 2011 15:39:48 GMT

--join 1 works

select 
    im.seq_num, 
    im.short_description, 
    sum( poli.quantity_ordered ) poli_qo 
from 
    purchase_order_header poh 
        join po_line_items poli on 
             poh.po_number = poli.po_number and 
             poh.bo_number = poli.bo_number 
        join inventory_master im on poli.seq_num = im.seq_num 
where 
    poh.po_status = 'o' 
group by 
    im.seq_num, 
    im.short_description ;

--join 2 works

select 
     im.seq_num, 
     im.short_description, 
     container_size,  
     sum( oli.quantity_ordered ) oli_qo
from 
     order_header oh 
          join order_line_items oli on oh.order_seq_num = oli.order_seq_num 
          join inventory_master im on oli.inventory_seq_num = im.seq_num 
where 
     oh.status in ( 'a', 'b', 'o', 'p' ) 
group by 
     im.seq_num,
     im.short_description, 
     container_size;

--join 3 fails

select 
     im.seq_num, 
     im.short_description, 
     container_size,  
     sum( oli.quantity_ordered ) oli_qo, 
     sum( poli.quantity_ordered ) poli_qo 
from 
     purchase_order_header poh 
          join po_line_items poli on 
               poh.po_number = poli.po_number and
               poh.bo_number = poli.bo_number 
    order_header oh 
          join order_line_items oli on oh.order_seq_num = oli.order_seq_num 
          join inventory_master im on oli.inventory_seq_num = im.seq_num 
          left outer join oli.inventory_seq_num = poli.seq_num 
where 
     oh.status in ( 'a', 'b', 'o', 'p' ) 
     and poh.po_status = 'o' 
group by 
     im.seq_num, 
     im.short_description, 
     container_size ;

the error message is

[Sybase][ODBC Driver][SQL Anywhere]Syntax error near 'order_header' on line 195

Re: Join on multiple tables not working

Former Member — Wed, 26 Oct 2011 16:01:24 GMT

If you format it a bit, you'll notice you need a comma or a join or something before "order_header." Just like the error message says, it's a syntax error.

join po_line_items poli 
       on poh.po_number = poli.po_number and 
          poh.bo_number = poli.bo_number 
       order_header oh 
  join order_line_items

Re: Join on multiple tables not working

t1950 — Wed, 26 Oct 2011 16:15:24 GMT

i've tried poh.po_number = poli.po_number and poh.bo_number = poli.po_number, <<< comma following

putting statements on separate lines (like your example)

deleting and poh.bo_number = poli.po_number thinking it was the second condition

switching order_header oh join order_line_items on... as the first join and moving purchase_order_header poh join po_line_items... to the second line

still gives me an error on the second line (table name changes)

should add i'm running ASA 11 (2331) 64 bit on a 2008 server sp1

Re: Join on multiple tables not working

Former Member — Wed, 26 Oct 2011 17:12:20 GMT

Well, I'm stabbing in the dark here, but... if this works in your second query:

order_header oh join order_line_items oli on oh.order_seq_num = oli.order_seq_num join inventory_master im on oli.inventory_seq_num = im.seq_num

Then this should also work, right? It's identical but ordered the other way around to make it easier to connect to the other query.

inventory_master im join order_line_items oli on im.seq_num = oli.inventory_seq_num join order_header oh on oli.order_seq_num = oh.order_seq_num

Now I think you should be able to use the same join to inventory master as you do in your first query:

join po_line_items poli on 
             poh.po_number = poli.po_number and 
             poh.bo_number = poli.bo_number
 join inventory_master im on poli.seq_num = im.seq_num 
 join order_line_items oli on im.seq_num = oli.inventory_seq_num
 join order_header oh on oli.order_seq_num = oh.order_seq_num

I don't know your data model, so I don't know why you've introduced an outer join in your 3rd query. I also haven't read your error messages, so I'm probably missing something really obvious. For your next error message, tell us what the code was and what the message said!

Re: Join on multiple tables not working

Breck_Carter — Wed, 26 Oct 2011 17:20:41 GMT

See the [missing ...] items below... but that just gets you past the syntax errors; the left outer join may have other problems since the ON predicate seems to refer to tables already defined earlier in the query:

select 
     im.seq_num, 
     im.short_description, 
     container_size,  
     sum( oli.quantity_ordered ) oli_qo, 
     sum( poli.quantity_ordered ) poli_qo 
from 
     purchase_order_header poh 
          join po_line_items poli on 
               poh.po_number = poli.po_number and
               poh.bo_number = poli.bo_number 
          join order_header oh [possible missing ON predicate]
          [missing join operator] order_line_items oli on oh.order_seq_num = oli.order_seq_num 
          join inventory_master im on oli.inventory_seq_num = im.seq_num 
          left outer join [missing table name] [missing ON keyword] oli.inventory_seq_num = poli.seq_num 
where 
     oh.status in ( 'a', 'b', 'o', 'p' ) 
     and poh.po_status = 'o' 
group by 
     im.seq_num, 
     im.short_description, 
     container_size ;

Re: Join on multiple tables not working

t1950 — Wed, 26 Oct 2011 18:00:26 GMT

carolstone wanted to know the error message.

the error message i'm getting from iSQL when i run the script below is: could not execute state. syntax error new 'oli' on line 12 SQLCODE = -131, ODBC 3 State='42000' Line 1, column 1

carolstone also asked about the outer join:
the query is looking for sales items that may or may not (outer join) have purchases coming in

i've put table creates pasted the "pretty" script at the end of this post. if Mr. Allen would pretty it up again, it will be easier to read.

you don't need data to get the error.

thanks for the suggestions

--drop table poh go create table poh ( po_number int not null, bo_number int not null, po_status char( 1 ) not null ) go create unique index kp_poh on poh ( po_number, bo_number ) go

--drop table poli go create table poli ( po_number int not null, bo_number int not null, line_number int not null, seq_num int not null, quantity_ordered int not null ) go create unique index kp_poli on poli ( po_number, bo_number, line_number ) go

--drop table oh go create table oh ( order_seq_num int not null, status char( 1 ) not null ) go create unique index kp_on on oh ( order_seq_num ) go

--drop table oli go create table oli ( order_seq_num int not null, line_number int not null, inventory_seq_num int not null, quantity_ordered int not null ) go create unique index kp_oli on oli ( order_seq_num, line_number ) go

--drop table im go create table im ( seq_num int not null, short_description char( 40 ) not null, container_size char( 20 ) not null ) go create unique index kp_im on im ( seq_num ) go

select im.seq_num, im.short_description, im.container_size,
sum( oli.quantity_ordered ) oli_qo, sum( poli.quantity_ordered ) poli_qo from poh join poli on poh.po_number = poli.po_number and poh.bo_number = poli.bo_number oh join oli on oh.order_seq_num = oli.order_seq_num join im on oli.inventory_seq_num = im.seq_num left outer join oli.inventory_seq_num = poli.seq_num where oh.status in ( 'a', 'b', 'o', 'p' ) and poh.po_status = 'o' group by im.seq_num, im.short_description, container_size ;

Re: Join on multiple tables not working

Former Member — Wed, 26 Oct 2011 18:37:00 GMT

Breck's comments are still exactly right. You're missing items.

select im.seq_num,
       im.short_description,
       im.container_size,
       sum( oli.quantity_ordered ) oli_qo,
       sum( poli.quantity_ordered ) poli_qo
  from poh
  join poli
      on poh.po_number = poli.po_number
     and poh.bo_number = poli.bo_number
**[missing join operator]** 
     oh
**[possible missing ON predicate]**
     join oli on oh.order_seq_num = oli.order_seq_num 
     join im on oli.inventory_seq_num = im.seq_num
     left outer join
**[missing table name]**
**[missing ON operator]**
     oli.inventory_seq_num = poli.seq_num
 where oh.status in ( 'a', 'b', 'o', 'p' ) 
   and poh.po_status = 'o'
 group by im.seq_num, im.short_description, container_size ;

I read your missing items as being items out of place. My suggestion brings back no errors, but it probably gives you the opposite of what you need:

select im.seq_num,
       im.short_description,
       im.container_size,
       sum( oli.quantity_ordered ) oli_qo,
       sum( poli.quantity_ordered ) poli_qo 
from poh
join poli on
     poh.po_number = poli.po_number and 
     poh.bo_number = poli.bo_number
 join im on poli.seq_num = im.seq_num 
 join oli on im.seq_num = oli.inventory_seq_num
 join oh on oli.order_seq_num = oh.order_seq_num
where oh.status in ( 'a', 'b', 'o', 'p' ) 
  and poh.po_status = 'o'
 group by im.seq_num, im.short_description, container_size ;

What my solution tells you is you can re-order your tables to get what you need. If you don't need to connect to the same table multiple times, don't do it. Just move the tables around until you get what you need. I can't stick around to finish this for you. But there should be enough here for you to get this figured out.

(To pretty-up your code, highlight your code and select the fifth icon from the left (it looks like 101 010), or press control-k. I haven't been able to make it work reliably in the comments section, but that's just me.)

Re: Join on multiple tables not working

t1950 — Fri, 28 Oct 2011 07:18:02 GMT

sorry i couldn't get back to this sooner.  had to make some money.  
i've tried all of the suggestions proposed above without positive results.
in english what i'm try to do is get all open sales orders (order_header OH and order_line_items OLI) 
that may or may not have pending purchase orders (purchase_order_header POH and po_line_items POLI) 
and link both transactions to inventory items (inventory_master IM)`
i need the link to inventory because our clients can "sell" services and buy non inventory items 
(pens and paper)

Re: Join on multiple tables not working

former_SQLA_member1694874 — Fri, 28 Oct 2011 09:49:47 GMT

hmmm it looked so pretty in the "Your answer" box.

Re: Join on multiple tables not working

former_SQLA_member1694874 — Fri, 28 Oct 2011 10:53:39 GMT

This really is my best guess, I've kept the group by but I'd have just used two sub selects to make it easy to read.

select im.seq_num, 
       im.short_description, 
       container_size, 
       oli_qo,
       (select sum(poli.quantity_ordered) 
        from   purchase_order_header poh 
               join po_line_items poli 
                 ON poh.po_number = poli.po_number 
                   AND poh.bo_number = poli.bo_number 
        where  im.seq_num = poli.seq_num 
                and poh.po_status = 'o') as poli_qo
from
(
SELECT im.seq_num, 
       im.short_description, 
       container_size, 
       SUM(oli.quantity_ordered)  oli_qo, 
FROM   order_header oh 
       JOIN order_line oli 
         ON oh.order_seq_num = oli.order_seq_num 
       JOIN inventory_master im 
         ON oli.inventory_seq_num = im.seq_num

WHERE  oh.status IN ( 'a', 'b', 'o', 'p' ) 
GROUP  BY im.seq_num, 
          im.short_description, 
          container_size; 
) as foo