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. 



Answer 1

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,

Answer 2

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.

Answer 3

yes, they can be different and that is what is throwing me off a little. 

Answer 4

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 

Answer 5

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:

Answer 6

thanks, i think that worked.