MySQL multiple tables and multiple rows

Question

Lets say I have a table in MySQL called articles, that has a column called article_id. I also have another table called links which have two columns called article_id and link.

Every article can have multiple links attached, let's say that article_id=2 have two links:

articles:
article_id | text
-----------+------
2          | blah

links:
article_id | url
-----------+------
2          | test1
2          | test2

Now I want a query that can somehow select the text from articles and the url from links where article_id=2 is that possible in one query?

Answer 1

You need to JOIN the tables together usung article_id. There are a few different types of join - if the article will always have links you can use INNER JOIN - if it might not then you can use LEFT JOIN, in which case url will be NULL.

SELECT a.text, l.url
FROM articles AS a
INNER JOIN links AS l ON a.article_id = l.article_id
WHERE a.article_id = 2

You should note that this will return one row for each url, with the article text repeated for each of these rows - in your application you can just take the text from the first row. If your text is large and there are many links for each article, it may be more efficient to do two queries - you'd have to profile it to find out.