select postalcode = case when p1 is not null then p1 else p2 end
from
(
	select distinct a.postalcode p1, t.postcalcode p2
	from business b 
	inner join franchiseregionvenue frv 
	on b.businessid = frv.businessid and frv.deletedate is null
	inner join regionpostalcoderef rp 
	on frv.regionid = rp.regionid
	full outer join
	(
		select a.postalcode 
		from business bb 
		inner join siteregionvenue srv 
		on bb.businessid = srv.businessid and srv.deletedate is null
		left join address a 
		on a.addressid = (select top 1 a1.addressid from Address a1 where a1.businessid = bb.businessid and a1.deletedate is null)
		inner join businesspermission bp 
		on bb.businessid = bp.businessid and bp.deletedate is null
		where bp.permittedbusinessid = 19844 
	) t
	on b.postcalcode = t.postalcode
	where b.businessid = 19844 AND (a.postalcode is null or t.postalcode is null)
)

select postalcode
from
(
	select distinct postalcode
	from business b 
	inner join franchiseregionvenue frv 
	on b.businessid = frv.businessid and frv.deletedate is null
	inner join regionpostalcoderef rp 
	on frv.regionid = rp.regionid
	where b.businessid = 19844
	union all
	select a.postalcode
	from business bb 
	inner join siteregionvenue srv 
	on bb.businessid = srv.businessid and srv.deletedate is null
	left join address a 
	on a.addressid = (select top 1 a1.addressid from Address a1 where a1.businessid = bb.businessid and a1.deletedate is null)
	inner join businesspermission bp 
	on bb.businessid = bp.businessid and bp.deletedate is null
	where bp.permittedbusinessid = 19844
) t
group by postalcode
having count(*) = 1

quote:

---

Originally posted by cjhardie

Sorry but that didn't quite work out the way I wanted any other suggestions?

---

quote:

---

Originally posted by cjhardie

It gave me and error message "Incorrect syntax near the keyword 'group'."

---

select		postalcode
from		(
			select distinct	postalcode
			from		business as b 
			inner join	franchiseregionvenue as frv on frv.businessid = b.businessid
			inner join	regionpostalcoderef as rp on rp.regionid = frv.regionid
			where		b.businessid = 19844
					and frv.deletedate is null

			union all

			select distinct	a.postalcode
			from		business as bb 
			inner join	siteregionvenue as srv on srv.businessid = bb.businessid
			inner join	businesspermission as bp on bp.businessid = bb.businessid
			inner join	(
						select		businessid,
								max(addressid) as addressid
						from		address
						where		deletedate is null
						group by	businessid
					) as a1 on a1.businessid = bb.businessid
			inner join	address as a on a.addressid = a1.addressid
			where		bp.permittedbusinessid = 19844
					and srv.deletedate is null
					and bp.deletedate is null
		) as x
group by	postalcode
having		count(*) = 1

quote:

---

Originally posted by cjhardie

OK what should the second query be not that it is fixed?
and the first query you gave me said "error incorrect syntax by )" On line 24 which was the last one.

---