SQL Query with a Join of the Same Table

I have a table with the users and credentials for an application.  Auditors want me to generate a report to make it so that they can audit when user accounts have been created and who they were created by.

So the table is:  sysid, userid, password, created_by, creation_date

The created_by column captures the userid of the person that created the account thru the application and the creation date.

I need to return every userid in the table (only 19 records) and display any records that were created by another id.  In essence, there is only one administrator that has created all of the records in the table, but the auditors want to know that the other userids did not create a record.

Does this make sense?  I believe that it can be accomplished with a join on the same table, but I am not sure of the syntax to make this happen.
mounty95Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
If you know the admin user id, why cant you run a query like this

select * from urTable where created_by <> 'the admin user id'
mounty95Author Commented:
It has to show all users that have access to the application and show the records that they have created even if they have not created any records.
Mark WillsTopic AdvisorCommented:
Not entirely sure I understand the problem, but lets say you want to get sysid and userid of the created_by person

Then you can do something like :

select s.sysid, s.userid, s.password, s.created_by, s.creation_date,c.sysid,c.userid
from yourtable S
left outer join yourtable C on S.created_by = C.userid


So you use the same table, but it is regarded as different by the use of Aliases - 'S' for source table, and 'C' for creator means you can access the same table as if there were two.

Does that make sense ?
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

mounty95Author Commented:
Mark,

That gets me closer to what I am looking for.  How do I have the other users listed with no records associated to them?  Like this:

User A   No records
User B   No records
User C    Created User A
User C    Created User B
User C     Created User D
User C     Created User E
User D    No records
User E     No records
Mark WillsTopic AdvisorCommented:
Thats why I used a LEFT OUTER JOIN - the entries in the second table dont have to exist.

If you want you can change the Alias's on the tables and you will get the creators and those they created.

select s.sysid, s.userid, s.password, s.created_by, s.creation_date,c.sysid,c.userid
from yourtable C
left outer join yourtable S on S.created_by = C.userid

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mounty95Author Commented:
Mark,
This worked perfectly.  Thank you very much for the help with this.  I greatly appreciate the help.
Patrick
Mark WillsTopic AdvisorCommented:
You are most welcome. It is my pleasure to be able to help :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.