CODEWITHSUNDEEP
mysqlsql
Lappies
Lappies

Reputation: 923

Using SQL variables with INNER JOINS

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;

Upvotes: 0

Views: 128

Answers (1)

Taryn
Taryn

Reputation: 247810

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;

Upvotes: 1

Related Questions