Mysql JOIN condition on another JOIN's statement results

Question

I need your help because JOINs are a headache for me and I am trying to use them as little as possible but that is not always easy...

I have these tables:

Transit table

Number, company, status

Subscription table

Userid, number, name

Event table

Number, event_type

Since now I used this query to get some info from 2 tables (transit and subscription) based on the conditions below (status = intransit and userid = 1):

SELECT transit.status, transit.company, subscription.name
FROM transit
    JOIN subscription ON transit.number = subscription.number 
        AND transit.status = 'intransit' 
        AND subscription.userid = 1

I don't know if it is the most efficient or correct but it worked.

Now I also want to add another condition but from a 3rd table. So I want the previous results to be norrowed down if the event_type = 1 (from the Event table). The only common value between the 3rd table and the results is the number. How can I add something like "AND event.event_id = 1" to the previous results so I get only those who satisfy that as well?

-edit- Since i tried the solution proposed below and to help others, for example the results are

12345 | companyA | intransit
56789 | companyB | intransit
39494 | companyC | intransit
58328 | companyB | intransit

but only number 56789 has event_type = 1 on the Event table, so I want only

56789 | companyB | intransit

to be returned. With the proposed query it returns

56789 | companyB | intransit
56789 | companyB | intransit
56789 | companyB | intransit

which is 3 times the correct row (as the rows NOT having the event_type = 1).

question from:https://stackoverflow.com/questions/65920660/mysql-join-condition-on-another-joins-statement-results

Answer 1

The query is

SELECT DISTINCT transit.status, transit.company, subscription.name
FROM transit
    JOIN subscription ON transit.number = subscription.number 
        AND transit.status = 'intransit' 
        AND subscription.userid = 1
    JOIN event ON event.number = transcript.number
        AND event.event_type = 1

docs for inner join here