CODEWITHSUNDEEP
mysqldatabasestringjoingroup-by
Sumit Kumar
Sumit Kumar

Reputation: 781

How to select distinct records when using Join?

I got 3 tables whose structure is like this:

//postsTable
// pid, userID, parentID, title, date

//userTable
// userID, username, loginDate

//tagTable
// id, pid, tag

When a new post is made, user can enter multiple tags, and each tag is stored in a separate row in tagTable.

Suppose user entered 3 tags.

Then, one row goes in postTable, and 3 rows go in tagTable

When I am selecting, I am using this query:

select p.*, c.*, t.* 
from postTable as p 
join userTable as c 
on p.userID = c.userID 
join tagTable as t 
on p.pid = t.pid
where p.parentID='0' 
order by p.date desc limit 10

I was hoping this will only select one record from postTable, and one out of 3 tags entered from tagTable and then it will skip to next row in postTable, ignoring the other 2 tags for the same post...

But its selecting 3 records, all duplicate, except the values for t.*

Basically, here is what I want.

select the post from postTable, and select one tag from tagTable and then skip to next row in postTable, ignore left out 2 tags from tagTable for the already selected post.

Something like distinct( p.pid ), c.userID, c.username, t.tag

What am I doing wrong here?

Upvotes: 0

Views: 45

Answers (1)

GMB
GMB

Reputation: 222432

Rather than picking a random tag out of those available for the post, a sensible option would be to use aggregation and group_concat(). This will give you one record per post, along with a comma-separated list of the associated tags:

select
    p.pid, 
    p.userID,
    p.parentID,
    p.title,
    p.date,
    u.userID,
    u.username,
    u.loginDate,
    group_concat(t.tag order by t.tag) tags
from 
    postTable as p 
    inner join userTable as u on on p.userID = u.userID 
    inner join tagTable as t on p.pid = t.pid 
where p.parentID = '0'
group by
    p.pid, 
    p.userID,
    p.parentID,
    p.title,
    p.date,
    u.userID,
    u.username,
    u.loginDate
order by p.date
limit 10

Upvotes: 1

Related Questions