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.
Thx for your reply.
AFAIK, JOINs are used for combining data from tables that have corresponding values.
This is not the case here. The tables are very much alike in structure, but not in content.
I've tried your statement, and now it only returns data from one of the tables.
My guess is I'm stuck with the UNION solution.
When I leave out the schmucking-up things, I get an error saying that the number of tables do not correspond.
When I enter the '' selector again, the query succeeds, but with no values for 'hoofdtype'.
I've also tried making sure that every field is getting it's own column by adding extra '' selectors, to no avail.
Any ideas ?
TIA,
Stijn
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
