Mysql check if table has any value

Question

Basically what I'm trying to do is to get a list of purchases from one table and check if there is any attachments associated with the purchase and display 1 or 0 if there is/isn't an attachment found.

SELECT
    CONCAT('#', LPAD(a.id, 5, '0')) as 'code',
    a.id, a.value_total, DATE_FORMAT(a.date, '%d/%m/%Y') as 'date',
    b.name as 'store',

    CASE WHEN a.notes IS NOT NULL
            THEN 1
            ELSE 0
        END AS notes

FROM
    tb_purchase a,
    tb_store b

WHERE a.id_store = b.id
AND a.id = :id_purchase // This line can be removed to get all the purchases or leave here to get just one with that specific ID

then I have a table called ts_purchase_att where I store the attachments relation between purchases and attachments. The table has this structure:

id_purchase | id_attachment
32          | 47
32          | 127
33          | 68
38          | 97

I don't need to get the attachments, just check if there is at least one attachment related to that purchase.

I tried using something like this but it doesn't work

LEFT JOIN
    ( SELECT 1 FROM ts_purchase_att c WHERE c.id_purchase = a.id ) as attachment

What am I doing wrong?

When you get an error, you should include the exact error message in your question. Your reputation score suggests you are not a newbie to Stack Overflow. You should know this! — Bill Karwin, Jun 29 '19 at 19:04
Please in code questions give a [mre]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); tags & clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) PS You have a syntax error. Read the grammar. Show as much as possible that constituent subexpressions are OK. Make it clear that your question is about *that error* & ask re your overall goal later in a new post. — philipxy, Jun 29 '19 at 20:48

score 3 · Answer 1 · answered Jun 29 '19 at 19:02

Use JOIN syntax consistently, it has been standard since 1992. Don't use the outdated comma-style joins. Mixing the two syntax styles is causing a real problem in your case.

The reason is that the JOIN operation has a higher precedence than the , join. So when you do this type of query:

FROM a, b LEFT OUTER JOIN c ON a.id = c.id_purchase

It first tries to evaluate the b LEFT OUTER JOIN c, but you're referencing the first table a in the join condition. MySQL hasn't even made that table alias yet, so it returns an error on references to columns belonging to a.

This is documented here: https://dev.mysql.com/doc/refman/8.0/en/join.html

JOIN has higher precedence than the comma operator (,), so the join expression t1, t2 JOIN t3 is interpreted as (t1, (t2 JOIN t3)), not as ((t1, t2) JOIN t3). This affects statements that use an ON clause because that clause can refer only to columns in the operands of the join, and the precedence affects interpretation of what those operands are.

See that section of the manual for more details and examples.

Here's the way to do the outer join combined with inner joins using JOIN syntax:

SELECT IF(c.id_purchase IS NULL, 0, 1) AS attachment_found
FROM tb_purchase a,
INNER JOIN tb_store b ON a.id_store = b.id
LEFT OUTER JOIN ts_purchase_att c ON a.id = c.id_purchase

*"The reason is that the JOIN operation has a higher precedence than the , join. "* To add to this didn't some MySQL versions handle this precedence wrong in the past when mixing these types... I believe that was the main reason i stopped mixing.. — Raymond Nijland, Jun 29 '19 at 19:07
Yes, joins were fixed in version 5.0.12 to conform with the ANSI SQL:2003 standard semantics. That version was released 2005-09-02 (almost 14 years ago as I write this), and has passed its end-of-support long ago. — Bill Karwin, Jun 29 '19 at 19:16
Yes i knew the precedence order handling was old but not that old i didn't expect MySQL 5.0... But i never trusted mixing again because off that and i believe i never used it since.. — Raymond Nijland, Jun 29 '19 at 19:30

score 0 · Answer 2 · answered Jun 29 '19 at 18:57

You need a LEFT JOIN:

SELECT DISTINCT
    CONCAT('#', LPAD(a.id, 5, '0')) as 'code',
    a.id, a.value_total, DATE_FORMAT(a.date, '%d/%m/%Y') as 'date'
    CASE 
      WHEN b.id IS NOT NULL THEN 1
      ELSE 0
    END AS notes
FROM tb_purchase a LEFT JOIN tb_store b
ON a.id_store = b.id

When there is no match in the table tb_store then b.id is null.

Mysql check if table has any value

2 Answers2