phantem
asked on
Complex queries
Hi all,
OK, so I've got 2 tables
Audit(UUID,UIDFrom,UIDTo,C hangeType)
and
People(UID,First,Last)
my actual tables are more complex than this, but it will serve for the time being.
What I'm trying to do is to create a query that will provide me with a table showing the content of the audit table, only instead of the UIDFrom, I want to see the UIDFrom person's last name -- and instead of UIDTo, I want that person's last name.
I know that I could just do three separate queries for each row of the audit table, but that seems very ineffieicnt.
Is there a better way to do this?
Thanks!
Chris
OK, so I've got 2 tables
Audit(UUID,UIDFrom,UIDTo,C
and
People(UID,First,Last)
my actual tables are more complex than this, but it will serve for the time being.
What I'm trying to do is to create a query that will provide me with a table showing the content of the audit table, only instead of the UIDFrom, I want to see the UIDFrom person's last name -- and instead of UIDTo, I want that person's last name.
I know that I could just do three separate queries for each row of the audit table, but that seems very ineffieicnt.
Is there a better way to do this?
Thanks!
Chris
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
yup as long as you give it a separate alias like p1 and p2
CJ
CJ
thnx for the "A"
CJ
CJ
ASKER
CJ,
If there is a row without a value for either the From or To values, that row is not returned... Can you think of a way around this?
The audit table holds 'new' records as well as 'updates', so some records would not have a 'uidFrom'...
Chris
If there is a row without a value for either the From or To values, that row is not returned... Can you think of a way around this?
The audit table holds 'new' records as well as 'updates', so some records would not have a 'uidFrom'...
Chris
you have to do an outer join.
select a.uuid, p1.Last, p2.Last, a.ChangeType
from audit a, people p1, people p2
where a.uidFrom = p1.uid(+)
and a.uidTo = p2.uid(+)
what this will do is if a corresponding row doesn't exist in the people table it will return a null rather than not return anything.
CJ
select a.uuid, p1.Last, p2.Last, a.ChangeType
from audit a, people p1, people p2
where a.uidFrom = p1.uid(+)
and a.uidTo = p2.uid(+)
what this will do is if a corresponding row doesn't exist in the people table it will return a null rather than not return anything.
CJ
ASKER
I just love quick solutions... I didn't realize that you could re-reference the same table in the from Clause
Chris