> Here is my solution: > (SELECT ID, referentie, postcode, gemeente, > kadastrale_opp,prijs_zichtbaar, verkoopprijs, status, adres, '' AS > hoofdtype FROM gronden WHERE status = 'te koop' ) UNION (SELECT ID, > referentie, postcode, gemeente, kadastrale_opp, prijs_zichtbaar, > verkoopprijs, doel AS status, straat AS adres, hoofdtype FROM panden > WHERE doel = 'te koop' ) ORDER BY verkoopprijs ASC > > As u can see, I am trying to schmuck up the field that is only present > in one table, by selecting nothing and naming it as that field name > (hoofdtype). > The query succeeds, but the row contains no data for hoofdtype.
UNION simply adds the records from each part to the result so far. So instead of adding hoofdtype data to the corresponding records that were already present in the result from the first query, the new records are simply appended to the record set so far. I gues you want to use a JOIN (probably a LEFT JOIN in your case) and tell MySQL which columns to use to find matching records in the second table and then combine the data from both tables in one record set. Suppose `referentie` is the column to use for matching data from both tables: SELECT g.`ID`, g.`referentie`, g.`postcode`, g.`gemeente`, g.`kadastrale_opp`, g.`prijs_zichtbaar`, g.`verkoopprijs`, g.`status`, g.`adres`, p.`hoofdtype` FROM `gronden` AS g LEFT JOIN `panden` AS p ON g.`referentie` = p.`referentie` WHERE g.`status` = 'te koop' ORDER BY g.`verkoopprijs` ASC ON g.`referentie` = p.`referentie` could be replaced with USING (`referentie`) since in both tables the field has the same name. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
