none
SQL Join

    Question

  • I have a Asset table and  a project table associated with it , the project is associated with a county and so is the asset but project can be in couple of counties so we track the county for asset individually, so if i wanted to write a query which will show the DataAsset, Project , DataAsset County , Project County how do i go about it ?   Usually i just do an inner join and when i do Counties.County i know its coming from the join but given there are two joins to county in this query i don't know how to approch this. Hope I could explain the issue i have included the diagram below if it helps. 

    
    Wednesday, July 02, 2014 3:39 PM

Answers

  • SELECT *
      FROM Project P
        INNER JOIN DataAssest DA
    	  ON P.apexID = DA.ID
        INNER JOIN Counties C
    	  ON DA.CountyID = C.ID
        INNER JOIN counties CP
              on P.CountyID = CP.ID

    I see. Simply join county again from the project:

    • Marked as answer by Nikhil_NK Wednesday, July 02, 2014 5:09 PM
    Wednesday, July 02, 2014 5:06 PM

All replies

  • Hi,

    Can you clarify something: a project can have a county and a data asset, and a data asset can have a county. Can these counties ever be different?

    If not, then you need not worry about the two joins to Counties. Just do the one from Project (or DataAsset).

    Hope this helps,

    Wednesday, July 02, 2014 3:56 PM
  • SELECT *
      FROM Project P
        INNER JOIN DataAssest DA
    	  ON P.apexID = DA.ID
        INNER JOIN Countries C
    	  ON DA.CountyID = C.ID

    I had to guess at some of the keys because they are not all shown, but something like this should work for you.

    Wednesday, July 02, 2014 3:58 PM
  • yes, they can be different and that is what is throwing me off a little. 
    Wednesday, July 02, 2014 4:46 PM
  • thanks , for the reply Patrick like the above question the project and the data asset can have different counties and i am not sure if the above query can show both the project county and also the data asset county accurately 
    Wednesday, July 02, 2014 4:48 PM
  • SELECT *
      FROM Project P
        INNER JOIN DataAssest DA
    	  ON P.apexID = DA.ID
        INNER JOIN Counties C
    	  ON DA.CountyID = C.ID
        INNER JOIN counties CP
              on P.CountyID = CP.ID

    I see. Simply join county again from the project:

    • Marked as answer by Nikhil_NK Wednesday, July 02, 2014 5:09 PM
    Wednesday, July 02, 2014 5:06 PM
  • thanks, i think that worked. 
    Wednesday, July 02, 2014 5:10 PM