SELECT * FROM 
(SELECT P.vehicleref,P.manufacturer,P.model,P.derivative,P.additionalfreetext,P.isLimited,
P.isPriceReduction,P.isSpecial, P.created, P.updated, NMF.term, NMF.milespa, 
NMF.maintained, NMF.ch,p.source, 
ROW_NUMBER() OVER ( PARTITION BY P.VehicleRef ORDER BY NMF.ch ) AS MinCH 
FROM ( SELECT * FROM vwAllMatrixWithLombardSimple ) P 
INNER JOIN dbPubMatrix..tblNewMatrixFigures NMF ON P.VehicleRef = NMF. [VehicleRef] 
WHERE type='car' AND LOCatCode IN (1,2,3,4,5,6)) Q WHERE MinCH = 1 
ORDER BY ch ASC

Select * from Tbl1 t1 join tblWebMatrixFigures t2 on t1.key = t2.key and t1.source = 'WEB'
union all
Select * from Tbl1 t1 join tblNewMatrixFigures t2 on t1.key = t2.key and t1.source <> 'WEB'

quote:

---

Originally posted by harsh_athalye

Select * from Tbl1 t1 join tblWebMatrixFigures t2 on t1.key = t2.key and t.source = 'WEB'
union all
Select * from Tbl1 t1 join tblNewMatrixFigures t2 on t1.key = t2.key and t.source <> 'WEB'

---

quote:

---

Originally posted by Peso

quote:

---

Originally posted by harsh_athalye

Select * from Tbl1 t1 join tblWebMatrixFigures t2 on t1.key = t2.key and t.source = 'WEB'
union all
Select * from Tbl1 t1 join tblNewMatrixFigures t2 on t1.key = t2.key and t.source <> 'WEB'

---

Equals

Select * from Tbl1 t1 join tblNewMatrixFigures t2 on t1.key = t2.key
where t.source is not null

---

E 12°55'05.25"
N 56°04'39.16"

---

quote:

---

Originally posted by harsh_athalye

How come? There is no LEFT JOIN involved.
And where is tblWebMatrixFigures table?

---

quote:

---

Originally posted by Peso

quote:

---

Originally posted by harsh_athalye

How come? There is no LEFT JOIN involved.
And where is tblWebMatrixFigures table?

---

Think about it.
You are SELECTing the exact same records regardingn to JOINing.
The only thing that is different is the t.Source filter.

For first SELECT you want all records where Source IS EQUAL TO 'Web'.
In second SELECT you want all records where Source IS NOT EQUAL TO 'Web'.

Which mean you got all records not equal to NULL.

---

E 12°55'05.25"
N 56°04'39.16"

---

NMF.term,
NMF.milespa,
NMF.maintained,
NMF.ch

SELECT		q.vehicleref,
		q.manufacturer,
		q.model,
		q.derivative,
		q.additionalfreetext,
		q.isLimited,
		q.isPriceReduction,
		q.isSpecial,
		q.created,
		q.updated,
		case when q.source = 'web' then q.term2 else q.term1 end AS term
		case when q.source = 'web' then q.milespa2 else q.milespa1 end AS milespa
		case when q.source = 'web' then q.maintained2 else q.maintained1 end AS maintained
		case when q.source = 'web' then q.ch2 else q.ch1 end AS ch,
		q.source
FROM		(
			SELECT		P.vehicleref,
					P.manufacturer,
					P.model,
					P.derivative,
					P.additionalfreetext,
					P.isLimited,
					P.isPriceReduction,
					P.isSpecial,
					P.created,
					P.updated,
					NMF1.term1,
					NMF1.milespa1,
					NMF1.maintained1,
					NMF1.ch1,
					p.source,
					NMF2.term2,
					NMF2.milespa2,
					NMF2.maintained2,
					NMF2.ch2,
					ROW_NUMBER() OVER (PARTITION BY case when p.source = 'web' then 0 else 1 end, P.VehicleRef ORDER BY NMF.ch) AS MinCH 
			FROM		vwAllMatrixWithLombardSimple AS p
			LEFT JOIN	dbPubMatrix..tblNewMatrixFigures as NMF1 ON NMF1.[VehicleRef] = P.VehicleRef
						AND p.source <> 'web'
			LEFT JOIN	dbPubMatrix..tblWebMatrixFigures AS NMF2 ON NMF2.[VehicleRef] = P.VehicleRef
						AND p.source = 'web'
			WHERE		p.type = 'car'
					AND p.LOCatCode IN (1, 2, 3, 4, 5, 6)
		) as Q
WHERE		q.MinCH = 1
order by	case when q.source = 'web' then q.ch2 else q.ch1 end