Joshua Joshua - 7 months ago 10
SQL Question

How can I join two join queries into one?

I'm kinda lost here I need to join two join queries to form one table. Here's the script that I'm working on...

SELECT Email, Domain, site.SiteID, site.SiteName, Status, LastLogin, up.UPID, up.UPName, trd.TRMCID, trd.TRMCName
FROM UserDetails as ud
INNER JOIN SiteLocation as site
on ud.SiteID = site.SiteID

INNER JOIN UltimateParent as up
on ud.UPID = up.UPID

INNER JOIN TRMCDetails as trd
on ud.TRMCID = trd.TRMCID

SELECT SiteID, cinfo.CountryCode, Country
From Country as cntry
INNER JOIN CountryInfo as cinfo
on cntry.CountryCode = cinfo.CountryCode

INNER JOIN SiteLocation as site2
on cntry.CountryCode = site2.CountryCode


I need to show the country but the countrycode is connected with the SiteID. I'm having difficulty to join the both of them.

This is the first table looks like

Email-Domain-SiteID-SiteName-Status-LLogin-UPID-UPName-TRMCID-TRMCName

This is the second table looks like

SiteID-CountryCode-Country

I need the third table to look like this

Email-Domain-SiteID-SiteName-Status-LLogin-UPID-UPName-TRMCID-TRMCName-Country

But, I'm having trouble combining the two joins I made prior.

Thanks for your help in advance!

Cheers!

Answer

Try Like :

SELECT
    Email,
    Domain,
    site.SiteID,
    site.SiteName,
    Status,
    LastLogin,
    up.UPID,
    up.UPName,
    trd.TRMCID,
    trd.TRMCName,
    cinfo.CountryCode,
    cntry.Country
FROM UserDetails as ud
    INNER JOIN UltimateParent up on ud.UPID = up.UPID
    INNER JOIN TRMCDetails trd on ud.TRMCID = trd.TRMCID
    INNER JOIN CountryInfo cinfo on cntry.CountryCode = cinfo.CountryCode
    INNER JOIN SiteLocation site on ud.SiteID = site.SiteID
    INNER JOIN Country cntry on cntry.CountryCode = site.CountryCode