[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: UNION (was: WHERE ... IN () )
From:       "Jigal van Hemert" <jigal () spill ! nl>
Date:       2005-01-31 15:59:02
Message-ID: 093201c507ad$fb272370$2801a8c0 () localdomain
[Download RAW message or body]

> 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/mysql?unsub=mysql@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]