CODEWITHSUNDEEP
sqlms-access
noelmcg
noelmcg

Reputation: 1067

Creating a query that queries the data in a table more than once

I have a table of data of personal details of clients:

ID
name
addressID
referralID

Part of the data is a 'referralID'. This will be filled in for some entries if they have been referred by other clients in the database. Therefore the referralID will match up to an ID in the database.

I'm trying to create a query that will return the details of the referrers and the names of the people that they have referred e.g:

Referrals ID (Where referallID = ID), 
Referrals name (name), 
ID of client referred (ID), 
Name of Client referred (name)

I'm having difficulty in how to approach this and what method to undertake as the query needs to reference itself in some way? How can i extract the details twice from the table?

Hope that is easy enough for someone to understand. Any help or guidance would be greatly appreciated, cheers

Upvotes: 0

Views: 60

Answers (2)

xQbert
xQbert

Reputation: 35333

Assuming you want a list of all clients and the people they referred and that client is the table name and that ID=ReferallID...

Select * from client c1
LEFT join client c2 on C1.ID = C2.ReferallID

Upvotes: 0

JonH
JonH

Reputation: 33153

Replace MyTable with the name of your table and join to the same table using aliases:

SELECT 
     m.ID, 
     m.Name, 
     m.AddressID, 
     m2.name as ReferralName 
FROM 
     MyTable m
INNER JOIN 
     MyTable m2 
ON m2.ReferralID = m.ID

Upvotes: 4

Related Questions