Problem with INNER JOIN

Question

Problem with INNER JOIN

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I am using INNER JOIN to list the demos and its descripcion , but now I need to add to this info the tags which are saved in a table tags with a name and a demo_id that would be the way to associate each tag with its demo since this would be the same as id of the table demos .

Try to make this query and technically it works, but what it does is for example if there are 2 tags for that demo repeat the demo 2 times with each tag.

SELECT 'i'.'name', 
       'i'.'id', 
       'i'.'img', 
       'i'.'cat_id', 
       'd'.'description', 
       'd'.'url', 
       t.name as tags 
FROM 'demos' i 
INNER JOIN 'details' d ON 'i'.'id' = 'd'.'demo_id' 
INNER JOIN tags t ON t.demo_id = i.id 
ORDER BY 'i'.'id' ASC

The result that returns this is:

[
  {
    "name": "asdasda",
    "id": "a12qdv",
    "img": "https://xxx.s3.amazonaws.com/demos/1490127164214",
    "cat_id": 0,
    "description": "asdas",
    "url": "http://asd/cms/demos",
    "tags": "ReactJS"
  },
  {
    "name": "asdasda",
    "id": "a12qdv",
    "img": "https://xxx.s3.amazonaws.com/demos/1490127164214",
    "cat_id": 0,
    "description": "asdas",
    "url": "http://asd/cms/demos",
    "tags": "PHP"
  }
]

What I need to return, suppose there are 3 demos:

[
  {
    "name": "asdasda",
    "id": "a12qrgw21dv",
    "img": "https://xxx.s3.amazonaws.com/demos/1490127164214",
    "cat_id": 0,
    "description": "asdas",
    "url": "http://asd/cms/demos",
    "tags": "ReactJS, JavaScript"
  },
  {
    "name": "asdasda",
    "id": "a12qdv",
    "img": "https://xxx.s3.amazonaws.com/demos/1490127164214",
    "cat_id": 0,
    "description": "asdas",
    "url": "http://asd/cms/demos",
    "tags": "PHP, ReactJS"
  },
   {
    "name": "asdasda",
    "id": "1231f2q21dv",
    "img": "https://xxx.s3.amazonaws.com/demos/1490127164214",
    "cat_id": 0,
    "description": "asdas",
    "url": "http://asd/cms/demos",
    "tags": "PHP, NodeJS, Phyton"
  }
]

mysql sql nodejs

asked by Santiago D'Antuoni 21.03.2017 в 22:02

source

2 answers

0

You could do a group by including the whole query, or you could make a table derived from the form

SELECT demo_id, GROUP_CONCAT(name) as names GROUP BY demo_id

And your query would be

SELECT 'i'.'name', 
       'i'.'id', 
       'i'.'img', 
       'i'.'cat_id', 
       'd'.'description', 
       'd'.'url', 
       t.names as tags 
FROM 'demos' i 
INNER JOIN 'details' d ON 'i'.'id' = 'd'.'demo_id' 
INNER JOIN (SELECT demo_id, GROUP_CONCAT(name) as names GROUP BY demo_id) t ON t.demo_id = i.id 
ORDER BY 'i'.'id' ASC

answered by 21.03.2017 в 22:12

Error when implementing a TAD Point in C ++ update dynamic HTML table row

score 1 · Accepted Answer

Try this:

SELECT 'i'.'name', 
       'i'.'id', 
       'i'.'img', 
       'i'.'cat_id', 
       'd'.'description', 
       'd'.'url',
       GROUP_CONCAT(t.name SEPARATOR ', ') as tags
FROM 'demos' i 
INNER JOIN 'details' d ON 'i'.'id' = 'd'.'demo_id' 
INNER JOIN tags t ON t.demo_id = i.id 
GROUP BY 'i'.'id'
ORDER BY 'i'.'id' ASC