Board index » delphi » SQL join with right table nulls

SQL join with right table nulls

I have two tables:  Table1 and Table2.

Table1 (15 rows)
------------------------------
progid
moduleid
optionid
active

Table2 (1 row)
------------------------------
progid
moduleid
optionid
groupid
rights

I want a result set that contains
------------------------------------------------------
Table1.progid, Table1.moduleid, Table1.optionid, Table1.active,
Table2.GroupID, Table2.rights

Table2 will not have a row for every row in Table1.
I want all rows in Table1 where (Table1.progid=Table2.progid) and
(table1.active=1)
I want the returned rows from above to only include Table2.groupid = "ADM"
When a match is fournd I want the value of Table2.rights returned in the
row.

Whatever I try, I get the value of Table2.rights returned in every row, but
I only have 1 row in Table2.  Thus, the single value of Table2.groupid and
Table2.rights is duplicated in every returned row.  When no match is found
then Table2.groupid and Table2.rights should be null or some specified
value.

I am using Access 2000
My current effort is below
SELECT Table1.PROGID, Table1.MODULEID, Table1.PROGOPTID, Table1.Active,
Table2.GROUPID, Table2.RIGHTS
FROM Table2.RIGHT JOIN Table1 ON Table2.PROGID = Table1.PROGID
WHERE (((Table1.Active)=1) AND ((Table2.GROUPID)="ADM"));

Any help is appreciated.
Thank you,
Steve

 

Re:SQL join with right table nulls


Quote
Steven Helms wrote:

> Table1 (15 rows)
> ------------------------------
> progid
> moduleid
> optionid
> active

> Table2 (1 row)
> ------------------------------
> progid
> moduleid
> optionid
> groupid
> rights

> I want a result set that contains
> ------------------------------------------------------
> Table1.progid, Table1.moduleid, Table1.optionid, Table1.active,
> Table2.GroupID, Table2.rights

> Table2 will not have a row for every row in Table1.
> I want all rows in Table1 where (Table1.progid=Table2.progid) and
> (table1.active=1)
> I want the returned rows from above to only include Table2.groupid =
> "ADM" When a match is fournd I want the value of Table2.rights
> returned in the row.

> Whatever I try, I get the value of Table2.rights returned in every
> row, but I only have 1 row in Table2.  Thus, the single value of
> Table2.groupid and Table2.rights is duplicated in every returned row.
> When no match is found then Table2.groupid and Table2.rights should
> be null or some specified value.

> I am using Access 2000
> SELECT Table1.PROGID, Table1.MODULEID, Table1.PROGOPTID,
> Table1.Active, Table2.GROUPID, Table2.RIGHTS
> FROM Table2.RIGHT JOIN Table1 ON Table2.PROGID = Table1.PROGID
> WHERE (((Table1.Active)=1) AND ((Table2.GROUPID)="ADM"));

Try using a LEFT join. Technically you should be able to make it work in
either direction, but since it is table 1 you want every record from, it
makes more sense for it to be the left table.
 SELECT  [...]
 FROM Table1
 LEFT JOIN Table2 ON Table2.PROGID = Table1.PROGID
   AND Table2.GROUPID='ADM'
 WHERE Table1.Active=1 ;

Note I've made the test for ADM as part of the join instead of the Where
clause. That is because putting it in the where clause places that limit on
the entire result set, meaning you won't get all rows you want from table 1
(essentially making it an INNER join). Putting it in the Join makes it part
of the join meaning it only limits Table 2, not Table 1.

Minor note: literal values like ADM should be enclosed in single quotes.

Note that I do not use Access so I'm only assuming it will handle such joins
the same as it does in, for example, Interbase.

--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: http://www.logicfundamentals.com/RADBooks.html
"It is error alone which needs the support of government. Truth can
stand by itself." - Thomas Jefferson

Re:SQL join with right table nulls


Hi Wayne,
Thank you very much.  Your suggestion worked, (with a few added parenthesis)
and moved me along in the project.  Have a great weekend.
Steve

Other Threads