• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

SQL Query Question


I need to wirte an sql query that pulls out info on clients. Now for each client record I have 2 codes for the countryID and nationalityID. I need to display both of them.

The problem I have is that these two fields point to the same table holding the data i need, As an example, assume I have the following:

ID 1 County Mallta Nationality Maltese
ID 2 Country UK Nationality British

Now I can have a client with a street address pointing to countryID 1, that is Malta, but a nationalituID of 2 = British.

Since now via left joins I added the country to the records I need to pull, but now I am stcuk as I want to add the nationality too.

Any Help is welcome, if you need further detail please just ask.
1 Solution
You can join them multiple times, but you have to give it a unique name.

select *
from YourTable inner join Country nat1 on yourable.nat2ID= nat1.id
                        inner join Country nat2 on yourtable.nat1ID = nat2.id
Imran Javed ZiaCommented:
for such condition you may use two joins as

Select * from yourTable
Inner Join tbl as Country On yourCondion
Inner Join tbl Nationailty  On yourCondion

if your data can be null then use left outer join as

Select * from yourTable
Left Outer Join tbl as Country On yourCondion
Left Outer Join tbl Nationailty  On yourCondion
ZeppiPAuthor Commented:
Perfect. Thanks mate. Did not know that you could join the same table more then once.

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now