如何摆脱这个双mysql连接中的重复？

Question

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());

}

Answer 1

According To you example quotes are related with user's id, then you should joinby user's id not by the group.

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

New Answer For Admin: (Admin can see all the quotes)

SELECT user_table.*, my_quotes.* 
FROM user_table
RIGHT JOIN my_quotes 
ON my_quotes.userid = user_table.userid //this will grab all the quotes