Re: I feel a bit dumb, but getting a bit clueless - Mailing list pgsql-admin

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
>
>
>
>
>