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

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.