CODEWITHSUNDEEP
mysql
Miha Šušteršič
Miha Šušteršič

Reputation: 10042

mysql query returning double rows

I am using a standard apache2 LAMP configuration (mysql and php5), and UTF8 encoding.

I have set up four tables.

The first one is named articles and has 6 columns:

id INT(11) AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(1000),
posted (TIMESTAMP)
author_id INT(11),
extract TEXT,
body TEXT

The second one is named authors

id INT(11) AUTO INCREMENT PRIMARY KEY,
name VARCHAR(100),
img VARCHAR(100),
bio TEXT

The third one is named categories

id INT(2) AUTO_INCREMENT PRIMARY KEY,
cat_name VARCHAR(100)

And the fourth one is named article_categories

id INT(2) AUTO_INCREMENT PRIMARY KEY,
article_id INT(11),
category_id INT(2)

Now what I want is to write a mysql query that will find the title, author name and cat_name of an article. So far I've come up with this, and it returns 10 rows, basically all the categories I've set up. (I've set up one article with 2 categories, and 5 categories in the categories table).

This is my query: 

SELECT articles.title, authors.name, categories.cat_name
FROM articles, authors, categories, article_categories 
WHERE title LIKE '%introduction%';

and my result:

+------------------------+-----------------------+--------------+
| title                  | name                  | cat_name     |
+------------------------+-----------------------+--------------+
| An introduction to ptc | Yannick Šušteršič     | free         |
| An introduction to ptc | Yannick Šušteršič     | free         |
| An introduction to ptc | Yannick Šušteršič     | members-only |
| An introduction to ptc | Yannick Šušteršič     | members-only |
| An introduction to ptc | Yannick Šušteršič     | milestone    |
| An introduction to ptc | Yannick Šušteršič     | milestone    |
| An introduction to ptc | Yannick Šušteršič     | basic        |
| An introduction to ptc | Yannick Šušteršič     | basic        |
| An introduction to ptc | Yannick Šušteršič     | advanced     |
| An introduction to ptc | Yannick Šušteršič     | advanced     |
+------------------------+-----------------------+--------------+

Is there a way to only display the article once, with all the corresponding categories name in a single row?

If not, how do I only get the relevant results? What am I doing wrong? Thanks

Upvotes: 1

Views: 75

Answers (2)

davidkonrad
davidkonrad

Reputation: 85518

You forget to compare author and articles vs categories :

SELECT 
   articles.title, authors.name, GROUP_CONCAT(categories.cat_name)         
FROM 
   articles, authors, categories, article_categories 
WHERE 
   title LIKE '%introduction%' 
   AND articles.author_id = authors.id 
   AND articles.id = article_categories.article_id 
   AND article_categories.category_id = categories.id;

Updated, this is the final correct statement, authored by OP.

Upvotes: 1

Renzo
Renzo

Reputation: 27414

You are doing what is called a times operation (cartesian product) instead of a join one, so that the result combines all the records, even if they are not related. What is missing in your query are the join conditions to restrict the result to only the records that are actually related, i.e.:

SELECT articles.title, authors.name, categories.cat_name
FROM articles, authors, categories, article_categories 
WHERE title LIKE '%introduction%' 
AND article_categories.article_id = articles.id
AND article_categories.category_id = categories.id
AND articles.author_id = authors.id

Upvotes: 1

Related Questions