How to merge two select queries resulting one table

Hi I have two select queries and I wanna merge them into a single table having 5 columns as Id, ClientId, Height, EyeColor, HairColor

开发者_运维知识库

Queries are:

SELECT ClientCharacteristic.Id
     , ClientCharacteristic.ClientId
     , ClientCharacteristic.Height
     , GeneralLookup.LookupItem as EyeColor  
FROM dbo.ClientCharacteristic
  INNER JOIN dbo.GeneralLookup
    ON GeneralLookup.Id=ClientCharacteristic.glEyeColorId 



SELECT ClientCharacteristic.Id
     , ClientCharacteristic.ClientId
     , ClientCharacteristic.Height
     , GeneralLookup.LookupItem as HairColor  
FROM dbo.ClientCharacteristic
  INNER JOIN dbo.GeneralLookup
    ON GeneralLookup.Id=ClientCharacteristic.glHairColorId

---

Generally, you'd use UNION to "merge" queries. However, in this particular case that would result in a single column containing both EyeColor and HairColor across otherwise duplicated rows. I doubt that's what you want. A better approach would likely be to alias your joined table so that you can join it twice:

SELECT
  ClientCharacteristic.Id,
  ClientCharacteristic.ClientId, 
  ClientCharacteristic.Height,
  EyeLookup.LookupItem as EyeColor,
  HairLookup.LookupItem as HairColor
FROM
  dbo.ClientCharacteristic
  INNER JOIN dbo.GeneralLookup AS EyeLookup
    ON EyeLookup.Id=ClientCharacteristic.glEyeColorId
  INNER JOIN dbo.GeneralLookup AS HairLookup
    ON HairLookup.Id=ClientCharacteristic.glHairColorId

The key thing to notice here is the AS clause in the INNER JOIN clauses, which aliases the joined table for the purpose of the rest of the query. This allows you to join the same table multiple times on different keys so that it can be referenced for different purposes.

---

union or union all should do it as long as the columns line up and are the same type (or can be implicitly converted)

Following Davids Advice and re-reading the question 5 columns would be

SELECT ClientCharacteristic.Id, 
       ClientCharacteristic.ClientId,  
       ClientCharacteristic.Height, 
       Eye.LookupItem as EyeColor
       Hair.LookupItem AS HairColor
FROM 
    dbo.ClientCharacteristic 
INNER JOIN 
    dbo.GeneralLookup Eye
    ON  Eye.Id=ClientCharacteristic.glEyeColorId     
INNER JOIN
    dbo.GeneralLookup Hair
    ON Hair.Id=ClientCharacteristic.glHairColorId 

---

You can use UNION to merge two queries into one.

---

SELECT ClientCharacteristic.Id, ClientCharacteristic.ClientId, ClientCharacteristic.Height, GeneralLookup.LookupItem as EyeColor, '' as HairColor    
FROM dbo.ClientCharacteristic INNER JOIN dbo.GeneralLookup ON GeneralLookup.Id=ClientCharacteristic.glEyeColorId
UNION
SELECT ClientCharacteristic.Id, ClientCharacteristic.ClientId, ClientCharacteristic.Height, '' as EyeColor, GeneralLookup.LookupItem as HairColor    
FROM dbo.ClientCharacteristic INNER JOIN dbo.GeneralLookup ON GeneralLookup.Id=ClientCharacteristic.glHairColorId

---

You can use de UNION operator like this:

SELECT ClientCharacteristic.Id, ClientCharacteristic.ClientId, ClientCharacteristic.Height, GeneralLookup.LookupItem as EyeColor
FROM dbo.ClientCharacteristic INNER JOIN dbo.GeneralLookup ON GeneralLookup.Id=ClientCharacteristic.glEyeColorId
UNION
SELECT ClientCharacteristic.Id, ClientCharacteristic.ClientId, ClientCharacteristic.Height, GeneralLookup.LookupItem as HairColor
FROM dbo.ClientCharacteristic INNER JOIN dbo.GeneralLookup ON GeneralLookup.Id=ClientCharacteristic.glHairColorId

Note that UNION operator discards duplicate tuples. To keep duplicate values in the resulting table, use UNION ALL.