Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Unwanted results in query...

chick80chick80 Member Posts: 349
Hi all,

I have 3 tables:
1) table users, with id, name, email etc etc etc
2) table groups, with id and name
3) table users2group which maps users to groups. It has id, id_user, id_group

Each user can be in one or more than one group.

Now, I want a query that, for a certain user, shows me all the groups and tell me which one is he in.
I'm using:

SELECT DISTINCT g.name, (g.id = u2g.id_group) AS ingroup
FROM groups AS g, users2group AS u2g, users AS u
WHERE u.id = AND u2g.id_user = u.id

This works if the user is just in one group and gives me something like:

group 1 | 0
group 2 | 1
group 3 | 0
group 4 | 0

if the user is in group 2

But, if the user is in group 2 and 3 I get (same query)

group 1 | 0
group 2 | 1 <----
group 2 | 0 <----
group 3 | 1 <====
group 3 | 0 <====
group 4 | 0

I'll be more than happy to hear any help/suggestion/solution you guys may have for me!

thanks
nico

[hr]
[italic]How beautiful, if Sorrow had not made sorrow more beautiful than Beauty itself.[/italic]
JOHN KEATS

Comments

  • HackmanCHackmanC Member Posts: 441
    Any kind of join in a single select will throw duplicated values
    in your sql statement, I believe this is the only way (to have
    correct values, without duplicates, and well structured) ...
    (And even the fastest with correct indexes.)

    [code][color=Green]select[/color] [color=Green]distinct[/color] [color=Blue]a[/color][color=Gray].[/color][color=Blue]idgrp[/color][color=Gray], [/color][color=Blue]a[/color][color=Gray].[/color][color=Blue]name[/color][color=Gray], [/color][color=Blue]b[/color][color=Gray].[/color][color=Blue]idgrp[/color] [color=Green]is[/color] [color=Green]not[/color] [color=Green]null[/color] [color=Green]as[/color] [color=Blue]exist[/color] [color=Green]from[/color] [color=Blue]groups[/color] [color=Blue]a[/color]
    [color=Green]left[/color] [color=Blue]join[/color] [color=Olive]([/color]
    [color=Green]select[/color] [color=Blue]c[/color][color=Gray].[/color][color=Blue]idgrp[/color][color=Gray], [/color][color=Blue]c[/color][color=Gray].[/color][color=Blue]name[/color][color=Gray], [/color][color=Blue]d[/color][color=Gray].[/color][color=Blue]pass[/color] [color=Green]from[/color] [color=Blue]userxgroup[/color] [color=Blue]c[/color]
    [color=Green]inner[/color] [color=Green]join[/color] [color=Blue]users[/color] [color=Blue]d[/color] [color=Green]on[/color] [color=Blue]c[/color][color=Gray].[/color][color=Blue]name[/color][color=Gray] = [/color][color=Blue]d[/color][color=Gray].[/color][color=Blue]name[/color]
    [color=Green]where[/color] [color=Blue]d[/color][color=Gray].[/color][color=Blue]name[/color][color=Gray] = [/color][color=Darkred]"[/color][color=Red]ADMIN[/color][color=Darkred]"[/color]
    [color=Olive])[/color] [color=Blue]b[/color] [color=Green]on[/color] [color=Blue]a[/color][color=Gray].[/color][color=Blue]idgrp[/color][color=Gray] = [/color][color=Blue]b[/color][color=Gray].[/color][color=Blue]idgrp[/color][/code]

    [red]Good luck![/red]
    [blue]Hackman[/blue]
  • chick80chick80 Member Posts: 349
    Thank you very much for your reply.
    I managed to solve the problem modifying your solution a little bit:

    [code]SELECT DISTINCT g.id, g.name, activeGrps.grpID IS NOT NULL AS ingroup
    FROM groups AS g
    LEFT JOIN (SELECT u2g.grpID, u2g.userID, u.name FROM users2groups AS u2g, users AS u
    WHERE u2g.userID = u.id AND u.id = 6) AS activeGrps
    ON activeGrps.groupID = g.id[/code]

    [hr]
    [italic]How beautiful, if Sorrow had not made sorrow more beautiful than Beauty itself.[/italic]
    JOHN KEATS
Sign In or Register to comment.