OraFAQ Forum: SQL & PL/SQL » subquery problem

Home » SQL & PL/SQL » SQL & PL/SQL » subquery problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

subquery problem [message #7490]

Tue, 17 June 2003 13:30

priya
Messages: 108
Registered: February 2000

Senior Member

I have two tables.

"Machine" table has Primary Key as host_id
-------------------------
host_id
ip
person
group_id
activitydate

"Group" has group_id as the Primary key
----------------------------
group_id
name
description
activity date

The group_id in Machine table is the foreign key for group_id in Group table.

When I add someone in machine.. I can either add a person or a group_id (not both).

Now I want to select everyone in Machine and just the name from Group table who has activity date from 1st Jan 2003 to today. How can I do this? Since some of the people in Machine may not have group..

I want to see all people from Jan to now.. and instead of selecting the group_id .. I want to select their group name..

Please help.

Report message to a moderator

Re: subquery problem [message #7491 is a reply to message #7490]

Tue, 17 June 2003 13:44

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

I think the outer join is what you're after:

SELECT m.host_id
,      m.ip
,      TO_CHAR(m.activity_date
       ,       'fmMM/DD/YYYY')  activity_date
,      g.name                   group_name
FROM   machine      m
,      group        g
WHERE  m.activity_date BETWEEN TO_DATE('20030101','YYYYMMDD')
                           AND SYSDATE
AND    m.group_id = g.group_id <b><font color=red>(+)</font></b>

Report message to a moderator

Re: subquery problem [message #7492 is a reply to message #7490]

Tue, 17 June 2003 13:51

Mike T
Messages: 32
Registered: August 2002

Member

You are looking for what's called an Outer Join (if you have Oracle 9, you can write this differently):

select m.person, g.name
from machine m, group g
where m.group_id = g.group_id (+)
and activity date >= '20030101'

I have no idea how your "activity date" field is set up, so you may have to play with the format of the 20030101 part, but this should get you closer to what you want.

Report message to a moderator