mysql query returning double rows

Question

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

Ok, I've set up the query with the help of the anwsers suggested, and now I get 2 rows. If I use GROUP by on them, it will only display one row, with one cat_name, but I need both. Is there a way to do this, or do I need to sort trough my results using PDO/jQuery? — Miha Šušteršič
– Miha Šušteršič, Commented Jun 13, 2015 at 9:39

davidkonrad · Accepted Answer · 2015-06-13 10:08:29Z

1

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.

edited Jun 13, 2015 at 10:08

answered Jun 13, 2015 at 9:25

davidkonrad

85.7k17 gold badges211 silver badges273 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Miha Šušteršič Over a year ago

This now returns 2 rows, the same article twice, with different categories. Is there a way to now display the two categories in a single row, under the cat_name column?

davidkonrad Over a year ago

@MihaŠušteršič, without knowing the data and unable to test, using distinct and exsplicitly refering to article_categories is what I can think of (updated answer)

Miha Šušteršič Over a year ago

the distinct actually does nothing here. I guess I can always set up 2 queries, and then work with that using PDO or jQuery. Thanks for the help

Miha Šušteršič Over a year ago

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;

Found what I was looking for, this is the final query. Update your anwser with it for future reference if you'd be so kind

davidkonrad Over a year ago

@MihaŠušteršič, have just done that. Great, perhaps it will help other people in the future.

Renzo · Accepted Answer · 2015-06-13 09:36:26Z

1

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

answered Jun 13, 2015 at 9:36

Renzo

27.6k5 gold badges54 silver badges64 bronze badges

Collectives™ on Stack Overflow

mysql query returning double rows

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related