To outer join or to inner join thats is the question

To outer join or to inner join thats is the question

Post by Insert UserNa » Fri, 18 Jan 2002 01:30:11



I have the following tables :

test1=# select * from radius_users;
 id |  name  | password |        pass_gen
----+--------+----------+------------------------
  6 | user 5 | MD5      | 2002-01-16 15:37:19+00
  4 | user 6 | MD5      | 2002-01-16 15:08:53+00
  1 | user 1 | MD5      | 2002-01-16 15:36:47+00

test1=# select * from radius_sections;
 id |     name
----+---------------
  2 | section Alpha
  3 | section_beta
  4 | section_omega

test1=# select * from radius_user_config ;
 user_id | section_id |   value
---------+------------+-----------
       6 |          4 | value_1
       6 |          3 | value_2

The value I have and need to base the entire query on is the USER_ID
(or ID in radius_users).  I need to return all the values in
radius_user_config that apply to the USER_ID specified but I also need
to know which sections are null and what their names are.

If I am not making sense here this is the result table I am after :

 id |  name  | name     |   value
----+--------+----------------+-----------
  6 | user 5 | section_alpha  | value_1
  6 | user 5 | section_beta   | value_2
  6 | user 5 | section_omega  |

id = radius_users.id
first name = radius_users.name
second name = radius_sections.name
value = radius_user_confif.value

Thanks in advance

 
 
 

To outer join or to inner join thats is the question

Post by Carl van Tas » Sat, 19 Jan 2002 23:54:07


Hi Insert :-)



>[...]
>The value I have and need to base the entire query on is the USER_ID
>(or ID in radius_users).  I need to return all the values in
>radius_user_config that apply to the USER_ID specified but I also need
>to know which sections are null and what their names are.

>If I am not making sense here this is the result table I am after :

> id |  name  | name     |   value
>----+--------+----------------+-----------
>  6 | user 5 | section_alpha  | value_1
>  6 | user 5 | section_beta   | value_2
>  6 | user 5 | section_omega  |

>[...]

select u.id, u.name, s.name, c.val
from radius_users u
       CROSS JOIN radius_sections s
         LEFT JOIN radius_user_config c
         ON (u.id = c.usrid and s.id = c.secid)
where u.id = 6;

gives
 id |  name  | name  | val
----+--------+-------+------
  6 | User 5 | Alpha |
  6 | User 5 | Beta  | val2
  6 | User 5 | Omega | val1

I hope this is, what you want, and your having put val1 into the alpha
line is simply a typo.

Kind regards
 Carl van Tast

 
 
 

1. One more If statement changes outer joint to Inner join

I did a merge today like this

data file3
merge file1 (in=a) file2(in=b);
by com4;
if a;
run;

everything worked fine: I got the numbers of obs that matches that of file1.

Then I added one more if statement:

data file3
merge file1 (in=a) file2(in=b);
by com4;
if a;
if (var1 NE 0) and (var2 NE =0);
run;

Then I got the numbers of obs that matches that of file2.

I know the fix. I can just change the data statement to

data file3 (where=(var1....));

Could somebody tell me why the 2nd If statement can change the join? Thanks.

Paula D

2. How to upgrade to msXML 2.0

3. One more If statement changes outer joint to Inner join: some clarifications

4. Obtaining multicast address

5. multi-table join, final table is outer join count ...

6. Reflex 30 Problem

7. Oracle outer join porting question

8. Writing Fiction with Dragon's NS

9. outer join question

10. Left Outer Join Question

11. Table alias and inner join in sql

12. inner join

13. Inner join syntax