Perform a left join with a second table after an inner join (on same table) after where conditions (Mysql)

Question

Here is my Database: bott_no_mgmt_data

random_no ; company_id ; bottle_no ; date       ; returned ; returned_to_stock ; username
    30201 ; MY COMP    ;         1 ; 2015-04-28 ;       10 ; NULL              ; ANDREW
    30202 ; MY COMP    ;         2 ; 2015-04-28 ;       10 ; NULL              ; ANDREW
    30205 ; MY COMP    ;         5 ; 2015-04-28 ;       10 ; NULL              ; ANDREW
    30208 ; MY COMP    ;         8 ; 2015-04-28 ;       10 ; NULL              ; ANDREW
    30209 ; MY COMP    ;         9 ; 2015-04-28 ;       10 ; NULL              ; ANDREW
    30210 ; MY COMP    ;        10 ; 2015-04-28 ;       10 ; NULL              ; ANDREW
    30211 ; MY COMP    ;         1 ; 2015-04-29 ;       20 ; NULL              ; ANDREW
    30212 ; MY COMP    ;         2 ; 2015-04-29 ;       20 ; NULL              ; ANDREW
    30213 ; MY COMP    ;         9 ; 2015-04-29 ;       30 ; NULL              ; ANDREW
    30214 ; MY COMP    ;        10 ; 2015-04-29 ;       30 ; NULL              ; ANDREW

I have successfully pulled all the entire unique rows from bott_no_mgmt_data where the field random_no is highest and bottle_no is unique with the following code:

select yt.* 
  from bott_no_mgmt_data yt<br>
       inner join(select bottle_no, max(random_no) random_no
                    from bott_no_mgmt_data 
                   WHERE username = 'ANDREW' 
                   group by bottle_no) ss on yt.bottle_no = ss.bottle_no 
                                         and yt.random_no = ss.random_no  
where returned < 15 and date > '2015-04-01'

So for example one of the rows it returns will be

30214;MY COMP;10;2015-04-29;30;NULL;ANDREW

and NOT

30210;MY COMP;10;2015-04-28;10;NULL;ANDREW

because while their bottleno's are the same the former's random_no is higher.

My Problem:

I now wish to compare each returned rows 'bottleno' with another table 'sample' which simply contains field 'bottleno' with a list of bottle numbers. I wish to compare them and only return those that match. I assume we would then 'LEFT JOIN' the results above with database 'sample' as below:

select yt.* from bott_no_mgmt_data yt<br>
       inner join(select bottle_no, max(random_no) random_no
                    from bott_no_mgmt_data WHERE username = 'ANDREW' 
                   group by bottle_no) ss on yt.bottle_no = ss.bottle_no and yt.random_no = ss.random_no 
 where returned < 15 and date > '2015-04-01'
       LEFT JOIN sample ON sample.bottleno = yt.bottle_no

The extra left join gives me an error

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN sample ON sample.bottleno = yt.bottleno WHERE sample.bottleno IS NULL ' at line 7

Thank you Dan, but surely it has to be after the where clause? I am comparing results from table A to table B only after having queried table A with a where cause? — Andrew Moir, Apr 28 '15 at 14:28

score 0 · Answer 1 · answered Apr 28 '15 at 14:29

0

All joins should be written before Where clause as Daan mentions:

select yt.* from bott_no_mgmt_data yt
inner join(
    select bottle_no, max(random_no) random_no 
    from bott_no_mgmt_data 
    WHERE username = 'ANDREW' group by bottle_no
)
ss on yt.bottle_no = ss.bottle_no and yt.random_no = ss.random_no 
LEFT JOIN sample ON sample.bottleno = yt.bottle_no
where returned < 15 and date > '2015-04-01'

answered Apr 28 '15 at 14:29

Dmitry Sadakov

2,128
3
19
34

cDima, thank you, quick query if there are 3 matches (with the same bottleno/bottle_no) in the sample database it returns 3 rows, however if there are no matches it still returns a single row? Any possible reason for returning a row when no match is possible? – Andrew Moir Apr 28 '15 at 15:00
It depends on the source data; in the no results case does the inner grouped select return anything at all? Perhaps making the INNER LEFT would remove the empty result - http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join – Dmitry Sadakov Apr 28 '15 at 15:16

James · Answer 2 · 2015-04-28T15:36:11.563

-1

A couple of things. You don't need that first inner join at all, it's pointless. Also, you said "I wish to compare them and only return those that match." - so that means you want INNER JOIN not LEFT JOIN.

SELECT MAX(random_no) AS random_no, company_id, yt.bottle_no, `date`, returned, username
FROM bott_no_mgmt_data yt
INNER JOIN sample ON sample.bottle_no=yt.bottle_no
WHERE yt.username = 'ANDREW' 
  AND yt.returned < 15 
  AND yt.date > '2015-04-01'
GROUP BY company_id, yt.bottle_no, `date`, returned, username

edited Apr 28 '15 at 15:36

answered Apr 28 '15 at 14:40

James

20,957
5
26
41

It's not pointless. It's necessary for "SELECT yt.*" to work. – Strawberry Apr 28 '15 at 14:52
@Strawberry true dat. I guess using the join is easier than specifying all the columns in the SELECT and GROUP BY clauses, but I bet it's not as efficient. – James Apr 28 '15 at 15:04
Specifying all the columns in the SELECT and GROUP BY clauses will not work. Of the various solutions that would work, the OP's solution is likely to be the most efficient. It's a standard uncorrelated subquery, as discussed in the manual, and *ad nauseam* elsewhere. – Strawberry Apr 28 '15 at 15:08
The correct rows according to the OP's spec are 30205 and 30208. That said, your other point about the OP's OUTER JOIN is correct. An OUTER JOIN is nonsensical in this context, where no columns from that joined table are required. – Strawberry Apr 28 '15 at 15:44
@Strawberry I see the problem and the difference in logic between this and the OP's post - thanks for taking the time to help me understand it. – James Apr 28 '15 at 15:57
You're welcome - it's confusing because the OP's own assertion about the rows returned is also incorrect. – Strawberry Apr 28 '15 at 16:00

Perform a left join with a second table after an inner join (on same table) after where conditions (Mysql)

2 Answers2