SELECT 1 as source, annonces.agenceID, reference, null as typeannonce, typeBien, annonces.code, annonces.ville, titre, prix, surface, nbpieces, nbchambres, description, photo1, photo2, photo3, photo4, photo5, urlvisitevirtuelle, honoraireslocation, honoraireslocationlocataire, depotgarantie, provisionchargeslocatives, descriptionEN, descriptioncourte, librele, ajout, null as mandat, agencesSite.externe, DPE, GES,
	typesBiens.*, villes.ville as villeref, annonces.CONDONB, annonces.CONDOCHARGES
	FROM annonces 
		left join agences on annonces.agenceID=agences.agenceID  
		left join agencesSite on agences.agenceID=agencesSite.agenceID 
		inner join typesBiens on annonces.typeBien=typesBiens.code1
		left join villes on annonces.ville=villes.variante
	WHERE typesBiens.lang='fr' AND replace(replace(reference,' ','-'),'°','-')='appartement-4-pia�ces' and annonces.siteID=98