SQL output multiple values

Question

I'm trying to get more values in a column and I'm testing it in phpMyAdmin:

I have a table Nieuws that contains in this case ID and a Title.

Table Nieuws

I have a table called Nieuws_tags where I give multiple tags to a item in column Nieuws.

table Nieuws_tags

The final column is called Tags that contains ID and Beschrijving

Table Tags

Here is what I currently have:

SELECT * FROM (`Nieuws`) JOIN `Tags` ON `Tags`.`ID` = `Nieuws`.`ID` 
WHERE `Tags`.`ID` = 1

1 | Titel 1 | 1 | QBNL

Right now I only get one value back QBNL, but I gave it two values QBNL and QBBC. I'm pretty sure that my code is wrong but hopefully someone can help me.

EDIT: What I want to achieve:

Most people here want formatted text, not images.

jarlh
– jarlh

2019-02-20 09:19:23 +00:00
Commented Feb 20, 2019 at 9:19 — jarlh
– jarlh, Commented Feb 20, 2019 at 9:19
Sorry about that.

Thijs van Rijn
– Thijs van Rijn

2019-02-20 09:51:32 +00:00
Commented Feb 20, 2019 at 9:51 — Thijs van Rijn
– Thijs van Rijn, Commented Feb 20, 2019 at 9:51

GMB · Accepted Answer · 2019-02-20 09:25:02Z

2

You need to bring in junction table Nieuws_tags :

SELECT * 
FROM `Nieuws`
INNER JOIN `Nieuws_tags` ON `Nieuws_tags`.`ID-Nieuws` = `Nieuws`.`ID` 
INNER JOIN `Tags` ON `Tags`.`ID` = `Nieuws_tags`.`ID-tags` 
WHERE `Tags`.`ID` = 1

edited Feb 20, 2019 at 9:25

answered Feb 20, 2019 at 9:13

GMB

224k25 gold badges103 silver badges151 bronze badges

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

9 Comments

Thijs van Rijn Over a year ago

Thank you GMB. Right now I'm getting a error: #1054 unknown column 'Nieuws.ID-tags' in on clause.

GMB Over a year ago

@ThijsvanRijn : sorry - should be Nieuws_tags.ID-tags

Thijs van Rijn Over a year ago

Is it correct that I still get one value back? Only QBNL and not QBNL and QBBC. I'm so sorry this is al new for me.

GMB Over a year ago

@ThijsvanRijn : do you want to filter by tag, or by news ?

GMB Over a year ago

In your data there is only one news that has tag 1, hence you get a single record with the current query. However there are two tags that relate to news 1, so if you change the WHERE clause to WHERE Nieuws.ID = 1 you will get rwo records.

|

Vijiy · Accepted Answer · 2019-02-20 09:52:21Z

0

Below would be the query if you need all the entries from tags.

SELECT * 
FROM Nieuws t1
INNER JOIN Nieuws_tags t2 ON t2.ID-Nieuws = t1.ID 
INNER JOIN Tags t3 ON t3.ID = t2.ID-tags 
WHERE t3.ID-Nieuws = 1

answered Feb 20, 2019 at 9:52

Vijiy

1,1977 silver badges23 bronze badges

Collectives™ on Stack Overflow

SQL output multiple values

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related