Stijn Verholen wrote:
Jigal van Hemert wrote:
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.
You mean "number of columns", don't you?
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 ?
I have one. Prior to mysql 4.1.1, column widths were determined by the first SELECT in the UNION. If you are using an earlier version, your first SELECT sets the column width for hoofdtype to 0, because you are selecting an empty string. You should be able to fix this by either reversing the order of your two SELECTs, or by replacing the empty string with a string filled with sufficient spaces. That is, change your query to
(SELECT
ID, referentie, postcode, gemeente, kadastrale_opp, prijs_zichtbaar,
verkoopprijs, doel AS status, straat AS adres, hoofdtype
FROM panden WHERE doel = 'te koop')
UNION
(SELECT
ID, referentie, postcode, gemeente, kadastrale_opp, prijs_zichtbaar,
verkoopprijs, status, adres, '' AS hoofdtype
FROM gronden WHERE status = 'te koop')
ORDER BY verkoopprijs ASCor something like:
(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 ASCTIA,
Stijn
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
