Re: I feel a bit dumb, but getting a bit clueless

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 (unless you 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 track non conformances assigned to each user so I select the count of non confomances assgined to them in a left join to show all users ...an inner join would only show users with non conformaces assigned to them...
>
> -----Original Message-----
> From: Michiel Lange [mailto:michiel(at)minas(dot)demon(dot)nl]
> Sent: Fri 11/7/2003 7:29 AM
> To: pgsql-admin(at)postgresql(dot)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
>
>
>
>
>