I want to fetch the data from three tables which have many to many relationship. Here is the table structure of my database.
users
uid[pk] fname lname username
1 John smith johnsmith
activity
act_id[pk] title
log Account Login
reg Account Registration
user_activity
serial_number[pk] act_id[fk] user_id[fk] ip_address date time
1 reg 1 2.2.2.2 -
2 log 1 1.1.1.1 -
I am trying to fetch:
title ip_address date time
Here is my mysqli query statement:
SELECT a.title, ua.ip_address, ua.date, ua.time FROM users u
LEFT JOIN user_activity ua ON ua.user_id = u.uid
LEFT JOIN activity a ON a.activity_id = ua.act_id
WHERE u.username=?"
It fetches the data from the database and i am getting 3 results instead of just 1:
Title Ip_address Date Time
Account Login 1.1.1.1 Mon,Nov4 2013 5:5pm
Account Login 1.1.1.1 Mon,Nov4 2013 5:5pm
Account Login 1.1.1.1 Mon,Nov4 2013 5:5pm
I used DISTICT but it does not work. Please make me correct if i am wrong. Thanks
If I understand you correctly, you just need to add a GROUP BY
SELECT
title, ip_address
FROM
users AS u
LEFT JOIN
user_activity AS ua ON ua.user_id = u.uid
LEFT JOIN
activity AS a ON a.activity_id = ua.act_id
WHERE
u.username = ?
GROUP BY
ua.ip_address
I figured out it myself. Now its working fine. Here is the query.
SELECT a.title, ua.ip_address, ua.date, ua.time
FROM users AS u
LEFT JOIN user_activity AS ua ON ua.user_id = u.uid
LEFT JOIN activity AS a ON a.activity_id = ua.act_id
WHERE u.username = 'johnsmith'
GROUP BY ua.serial_number