CODEWITHSUNDEEP
mysqlsqlinner-join
Milos Cuculovic
Milos Cuculovic

Reputation: 20223

INNER JOIN with a limit of 1

I have a query:

SELECT count(authors.id), country.name from authors
INNER JOIN articles ON authors.article_id =  articles.id
INNER JOIN author_affi_rel ON authors.id =  author_affi_rel.id_author
INNER JOIN affiliations ON author_affi_rel.id_affiliation = affiliations.id
INNER JOIN country ON affiliations.country = country.unique_id
WHERE articles.journal_id = 20 AND authors.correspondence =1
GROUP BY country.name
ORDER BY count(authors.id) DESC

where I am selecting the number of authors per country.

Everything works fine except that in the table author_affi_rel, I can have more than one affiliation per author, so in my result, the authors will be doubled.

My goal is to limit to one the 

INNER JOIN author_affi_rel ON authors.id =  author_affi_rel.id_author

I tryed to do:

INNER JOIN author_affi_rel ON authors.id =  (SELECT id_author FROM  author_affi_rel WHERE id_author =  authors.id LIMIT 1)

But my query is stocked there, no response.

Do you maybe know how to do a inner join and limit the result to only one single.

Thank you very much in advnce.

Upvotes: 1

Views: 216

Answers (1)

Benoit
Benoit

Reputation: 79185

Use COUNT(DISTINCT author_id) in your query, if performance is not an issue.

Upvotes: 4

Related Questions