Need help with a SQL query to marry Click and Subscriber data

Question

I'm trying to run a query that will return all of the clicks from a specific email send including alias and email. I need to then marry that data with a custom attribute in our subscriber data (STATUS1) that tells us what segment these people belong to. The query is below:

SELECT
a.JobID, a.SubscriberKey, a.LinkName, a.LinkContent
FROM _Click a
INNER JOIN (
    SELECT 
    JobID
    ,EmailName
    FROM _Job 
) b ON b.JobID = a.JobID
WHERE b.EmailName = 'My Email Name'
INNER JOIN (
    SELECT 
    STATUS1,
    EmailAddress
    FROM _Subscribers 
) c ON c.EmailAddress = a.SubscriberKey

This query works until I add the second inner join (I took that original query from this site). When I add the second Inner Join, I'm getting this error "An error occurred while checking the query syntax. Errors: Incorrect syntax near the keyword 'INNER'".

I can add the "STATUS1" data from the second inner join manually through excel since this is data for a report anyways. If this isn't doable with a custom subscriber attribute, I have another question. Is there a way to limit the results so that I only get one unique url (LinkContent) per SubscriberKey?

Thanks!

Answer 1

Answering based on RDBMS sql knowledge.

Inner Joins needs to be together and must come before where clause. You have added a where clause in between them.