CODEWITHSUNDEEP
mysqlsql
Calibur Victorious
Calibur Victorious

Reputation: 638

SELECT one row of a table, And all rows in another table

if i have these two tables:

[    id    -     title    -     content    ]
[    1    -     title1    -     content1   ]
[    2    -     title2    -     content2   ]

and

[    id    -     pid    -       tags   ]
[    1    -       1    -        TAG1   ]
[    2    -       1    -        TAG2   ]
[    3    -       1    -        TAG3   ]
[    4    -       2    -        TAG2   ]

Now what i'm trying to do is Select the title and content from table1 then all tags from table2 where b.pid = a.id

so my query is

SELECT a.title, a.content, b.tags
FROM table1 a LEFT JOIN table2 b ON a.id = b.pid
WHERE a.id = 1

What i want to get is

title1 content1 TAG1 TAG2 TAG3

but what i get is only TAG1 and repeated values of title1 content1 for each tag

Then i tried

SELECT a.title, a.content, 
 (SELECT DISTINCT b.tags)
  FROM table1 a LEFT JOIN table2 b
  WHERE a.id = 1

but still don't work as intended.

Upvotes: 0

Views: 1137

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

For ibtain the tags related to id on the same row you could use group_concat

  select  a.title, a.content, group_concat(b.tags)
  from table1 a LEFT JOIN table2 b ON a.id = b.pid
  WHERE a.id = 1
  group by  a.title, a.content

you can use count on the same query too

  select  a.title, a.content, group_concat(b.tags), count(*)
  from table1 a LEFT JOIN table2 b ON a.id = b.pid
  WHERE a.id = 1
  group by  a.title, a.content

for distinct tas you can use 

 select  a.title, a.content, group_concat(distinct b.tags)
  from table1 a LEFT JOIN table2 b ON a.id = b.pid
  WHERE a.id = 1
  group by  a.title, a.content

Upvotes: 2

Related Questions