Using SQL variables with INNER JOINS

Question

Im trying to use a variable to count the number of rows i have for a specific id, if i use it without inner joins it works perfectly, otherwise no, here is the section without joins

    select
                            cd.basis_point bpt,
                            cd.created,
                            @version:=@version+1 version
                        from tbl_class_det cd, (select @version:=1) v
                        where (cd.class_uuid='{$row['uuid']}')
                        and (cd.created>(
                                                        select
                                                            created
                                                        from tbl_class_det
                                                        where (class_uuid=cd.class_uuid)
                                                        order by created asc
                                                        limit 1
                                                    ))
                        order by cd.created asc

the section with joins that gives an error:

    select c.name,
                            fd.value bpt,
                            fd.created,
                            @version:=@version+1 version
                        from tbl_fee_det fd, (select @version:=1) v
                            INNER JOIN tbl_fee f ON f.uuid = fd.fee_uuid
                            INNER JOIN tbl_class c ON c.uuid = f.class_uuid
                        where (fd.created>(
                                                        select
                                                            created
                                                        from tbl_class_det
                                                        where (fee_uuid=f.uuid)
                                                        order by created asc
                                                        limit 1
                                                    ))
                        order by fd.created asc;

Feel like telling us what the error is? Also this looks like MySQL. You should tag it as such. — Conrad Frix
– Conrad Frix, Commented Jan 16, 2013 at 15:49
#1054 - Unknown column 'fd.fee_uuid' in 'on clause' ==> but then this column does exist — Lappies
– Lappies, Commented Jan 16, 2013 at 15:52
Not sure - this being mysql - but using fd.fee_uuid in the ON when the fd isn't part of that set of tables seems odd. fd is aliased to a table that is the "other side" of a , created outer join. — Jon Egerton
– Jon Egerton, Commented Jan 16, 2013 at 15:55
At a guess, it's an issue with mixing pre-ansi style joins (, ) and ansi-style joins ` JOIN ` in the FROM clause. Try moving the comma join to the end of the FROM clause. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jan 16, 2013 at 15:56

Taryn · Accepted Answer · 2013-01-16 15:57:08Z

1

You are using a combination of explicit and implicit joins. You should not mix the JOIN syntax with the comma syntax. If you need to you this then use a subquery similar to this:

select name,
    value bpt,
    created,
    @version:=@version+1 version
from
(
    select c.name,
        fd.value,
        fd.created,
        f.uuid
    FROM tbl_fee_det fd
    INNER JOIN tbl_fee f 
        ON f.uuid = fd.fee_uuid
    INNER JOIN tbl_class c 
        ON c.uuid = f.class_uuid
) f, (select @version:=1) v
where (created>(select created
                   from tbl_class_det
                   where (fee_uuid=f.uuid)
                   order by created asc
                   limit 1))
order by created asc;

answered Jan 16, 2013 at 15:57

Taryn

249k57 gold badges375 silver badges409 bronze badges

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

Collectives™ on Stack Overflow

Using SQL variables with INNER JOINS

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related