How can I take certain field value with a join without really joining that table?

Question

Well, I have a database from a client where tables have not a good relationship, is a mess. The problem to get the info I need is that the fields I need to relate the tables are not related with the tables I am going to use.

I'll make myself clear:

There are 3 tables: visits, coffees and payments.

Table coffees has payment_id. Table visits has coffee_id. Table payments has no ids!!

I want to know how many visits had coffee and how many payments were made for each coffee..

The result should be something like:

VISITS | COFFEE | PAYMENTS
8234 | MOCHA | 829

So, how am I going to know all the payments that were made, what coffee it was and how many visits it had?

Might be obvious but I am kind of overwhelmed with the same thing.

Thanks everyone for your help!

Answer 1

To get visits for each coffee:

SELECT c.name AS coffee, IFNULL(count(v.coffee_id), 0) AS visits
FROM coffees AS c
LEFT JOIN visits AS v ON c.id = v.coffee_id
GROUP BY c.id

To get payments for each coffee:

SELECT c.name AS coffee, IFNULL(count(p.id), 0) AS payments
FROM coffees AS c
LEFT JOIN payments AS p ON c.payment_id = p.id
GROUP BY c.id

To get them both in a single query:

SELECT coffee, MAX(visits) AS visits, MAX(payments) AS payments
FROM (
    SELECT c.name AS coffee, IFNULL(count(v.coffee_id), 0) AS visits, 0 AS payments
    FROM coffees AS c
    LEFT JOIN visits AS v ON c.id = v.coffee_id
    GROUP BY c.id
    UNION
    SELECT c.name AS coffee, 0 as visits, IFNULL(count(p.id), 0) AS payments
    FROM coffees AS c
    LEFT JOIN payments AS p ON c.payment_id = p.id
    GROUP BY c.id) x
GROUP BY coffee