I looked through other Q&A's and I didn't see my scenario.
My duplicates are weird. Different logged in managers would get different amount of duplicates depending on how many users are under them.
SELECT user_groups.*, user_table.*, my_quotes.*
FROM user_groups
RIGHT JOIN user_table
ON user_table.usergroup = user_groups.groupid
RIGHT JOIN my_quotes
ON my_quotes.groupid = user_groups.groupid
Need to display results from my_quotes, but while also displaying other info from other tables.
A quote belongs to a user and also to the manager. when a manager creates a quote for a user, but there happens to be other users that the manager manages, the quotes table from the query above will show a quote for every user even though the quote belongs to just one user.
So say there are 2 users under the manager. user 1 has a quote and user 2 has a quote. the quotes mysql table will only have 2 records so the html table should only display 2 records, but instead it displays 4 records. 2 for quote 1 and 2 for quote 2.
Now let's say another user is added under the manager. the html table will then display 3 records for each quote. it's weird.
The following doesn't work:
SELECT DISTINCT user_groups.*, user_table.*, my_quotes.*
FROM user_groups
RIGHT JOIN user_table
ON user_table.usergroup = user_groups.groupid
RIGHT JOIN my_quotes
ON my_quotes.groupid = user_groups.groupid
user_groups:
groupid, name, userid
user_table:
userid, username, email, usergroup, name, company, address, phone, manager, client
my_quotes:
id, userid, groupid, clientname, ponumber, date, postage, qty, price, total, approved
couple of the tables have too many fields to post. i just put the relevant ones to this problem.
one of the answers given helped, but there's still something wrong. as you can see, there are 3 different queries depending if logged in user is admin, manager, or client. the manager and client one displays correct records, and the admin one almost does, but there is a missing record for admin.
if($_SESSION['usergroup']==1) {
$tableresult = mysql_query('
SELECT user_groups.*, user_table.*, my_quotes.*
FROM user_groups
RIGHT JOIN user_table
ON user_table.usergroup = user_groups.groupid
RIGHT JOIN my_quotes
ON my_quotes.userid = user_table.userid
') or die(mysql_error());
} else if($manager==1) {
$tableresult = mysql_query('
SELECT user_groups.*, user_table.*, my_quotes.*
FROM user_groups
RIGHT JOIN user_table
ON user_table.usergroup = user_groups.groupid
RIGHT JOIN my_quotes
ON my_quotes.userid = user_table.userid
WHERE user_groups.userid='.$_SESSION['userid'].'
') or die(mysql_error());
} else {
$tableresult = mysql_query('
SELECT my_quotes.*, user_table.*
FROM my_quotes
INNER JOIN user_table
ON my_quotes.userid = user_table.userid
WHERE user_table.userid = '.$_SESSION['userid'].'
AND (approved = 1 OR approved = 2)
') or die(mysql_error());
}