Re: [ADMIN] I feel a bit dumb, but getting a bit clueless

[Michiel Lange]

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:[EMAIL PROTECTED] 
	Sent: Fri 11/7/2003 7:29 AM 
	To: [EMAIL PROTECTED] 
	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
        
 



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])