SQL Query Question

Posted on 2011-05-11
Last Modified: 2012-05-11

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.
Question by:ZeppiP
    LVL 53

    Accepted 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=
                            inner join Country nat2 on yourtable.nat1ID =
    LVL 16

    Expert Comment

    by:Imran Javed Zia
    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

    Author Closing Comment

    Perfect. Thanks mate. Did not know that you could join the same table more then once.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    6 Experts available now in Live!

    Get 1:1 Help Now