Обсуждение: I feel a bit dumb, but getting a bit clueless

I am certainly not awake, that I have troubles with a simple thing like
this, but there it is....

I have three tables: Names(NameID INT4 PRIM INDEX, Name VARCHAR(30)),
Groups(GroupID INT4 PRIM INDEX, Group VARCHAR(30))
And a table Members(NameID,GroupID) PRIM INDEX ON (NameID, GroupID)

Now I put some data in all three tables, first created some Names (a
grand total of 4) and a few groups (a grand total of 6)
And Members like this (NameID, GroupID)
1, 1
1, 2
1, 3
2, 1
2, 4
2, 5
3, 2
3, 6
4, 1

Now I want to show essentially the Members table, but the numbers should
be replaced by the Names that go with the ID.
When I try this query:
SELECT "Name", "Group" FROM "Names", "Groups" RIGT JOIN "Members" ON
'Names.NameID' = 'Members.NameID' AND 'Groups.GroupID' = 'Members.GroupID';

I get 24 results (6 times 4 = 24) so, it shows like all names are member
of all groups... That's not true...
If I change from a LEFT JOIN to a RIGHT JOIN, it gets even stranger: I
get 36 (4 * 9) results... but the name for Group is left empty.

So somewhere I make a huge thinking mistake... but getting a bit
clueless where I am going wrong...
Can someone help me out? It gets frustrating... :(

- feeling a bit silly too, this is basics... -
Michiel

On Fri, 7 Nov 2003, Michiel Lange wrote:

> I am certainly not awake, that I have troubles with a simple thing like
> this, but there it is....
>
> I have three tables: Names(NameID INT4 PRIM INDEX, Name VARCHAR(30)),
> Groups(GroupID INT4 PRIM INDEX, Group VARCHAR(30))
> And a table Members(NameID,GroupID) PRIM INDEX ON (NameID, GroupID)
>
> Now I put some data in all three tables, first created some Names (a
> grand total of 4) and a few groups (a grand total of 6)
> And Members like this (NameID, GroupID)
> 1, 1
> 1, 2
> 1, 3
> 2, 1
> 2, 4
> 2, 5
> 3, 2
> 3, 6
> 4, 1
>
> Now I want to show essentially the Members table, but the numbers should
> be replaced by the Names that go with the ID.
> When I try this query:
> SELECT "Name", "Group" FROM "Names", "Groups" RIGT JOIN "Members" ON
> 'Names.NameID' = 'Members.NameID' AND 'Groups.GroupID' = 'Members.GroupID';

I doubt this is the actual query since at the very least RIGHT is
misspelled. However, your ON conditions are testing constant strings, not
columns (single quotes rather than double quotes) and you cannot put the
Names to Members comparison in the ON because that'll be an error unless
you did something like:

SELECT "Name", "Group" FROM "Members" LEFT JOIN "Names" ON
"Names"."NameID" = "Members"."NameID" LEFT JOIN "Groups" ON
"Groups"."GroupID" = "Members"."GroupID";

The first join may be intended to be INNER if you want to drop people from
the output who don't have names. If you want to drop output rows where
neither existed you can add where "Name" is not null or "Group" is not
null to the end.

Yes, that's the one :)
Strange though that I really thought that I had done that first, but
that gave no results at all... might have had something to do with
double and single quotes ("Names"."NameID") is what I did now, and that
works, but 'Names.NameID' gave no results, but no error either... Left
me a bit clueless...
Thanks for the quick response tho!

Michiel

Jon Pastore wrote:

>wouldn't you want to do an inner join not a right join?
>
>something like:
>
>select names.*,groups.* from names,groups where groups.groupid=members.groupid and names.nameid=members.nameid
>
>I think a right join will match show everything in theright side of your test and fill with nulls where nothing exists
(unlessyou coalesce that) 
>
>I use that with left joins all the time...for example I have an ISO:9000 module we designed for our software that
tracknon conformances assigned to each user so I select the count of non confomances assgined to them in a left join to
showall users ...an inner join would only show users with non conformaces assigned to them...  
>
>    -----Original Message-----
>    From: Michiel Lange [mailto:michiel@minas.demon.nl]
>    Sent: Fri 11/7/2003 7:29 AM
>    To: pgsql-admin@postgresql.org
>    Cc:
>    Subject: [ADMIN] I feel a bit dumb, but getting a bit clueless
>
>
>
>    I am certainly not awake, that I have troubles with a simple thing like
>    this, but there it is....
>
>    I have three tables: Names(NameID INT4 PRIM INDEX, Name VARCHAR(30)),
>    Groups(GroupID INT4 PRIM INDEX, Group VARCHAR(30))
>    And a table Members(NameID,GroupID) PRIM INDEX ON (NameID, GroupID)
>
>    Now I put some data in all three tables, first created some Names (a
>    grand total of 4) and a few groups (a grand total of 6)
>    And Members like this (NameID, GroupID)
>    1, 1
>    1, 2
>    1, 3
>    2, 1
>    2, 4
>    2, 5
>    3, 2
>    3, 6
>    4, 1
>
>    Now I want to show essentially the Members table, but the numbers should
>    be replaced by the Names that go with the ID.
>    When I try this query:
>    SELECT "Name", "Group" FROM "Names", "Groups" RIGT JOIN "Members" ON
>    'Names.NameID' = 'Members.NameID' AND 'Groups.GroupID' = 'Members.GroupID';
>
>    I get 24 results (6 times 4 = 24) so, it shows like all names are member
>    of all groups... That's not true...
>    If I change from a LEFT JOIN to a RIGHT JOIN, it gets even stranger: I
>    get 36 (4 * 9) results... but the name for Group is left empty.
>
>    So somewhere I make a huge thinking mistake... but getting a bit
>    clueless where I am going wrong...
>    Can someone help me out? It gets frustrating... :(
>
>    - feeling a bit silly too, this is basics... -
>    Michiel
>
>
>
>
>    ---------------------------(end of broadcast)---------------------------
>    TIP 4: Don't 'kill -9' the postmaster
>
>
>
>
>