|
|
Posted by Rik on 12/12/06 02:07
GarryJones wrote:
> I think the following statement ....
>
> $ml_collect='SELECT *, DATE(CONCAT(field1, field2)) AS thedate FROM
> ml_lopp LEFT JOIN scfmforening ON (scfmforening.scfmnum =
> ml_lopp.scfmnum) LEFT JOIN ml_tidplats ON (ml_tidplats.loppnum =
> ml_lopp.loppnum) ORDER BY thedate';
>
> ....would work if "field1" and "field2" were in the table "ml_lopp"
>
> However, "field1" and "field2" are in the table "ml_tidplats" and
> accessed by "LEFT JOIN".
>
> So, how do I use "DATE(CONCAT(field1, field2))" in this case?
When all else fails, try to get them explicitly:
SELECT
`ml_lopp`.*,
DATE(CONCAT(`ml_tidplats`.`field1`, `ml_tidplats`.`field2`)) AS
'thedate'
FROM `ml_lopp`
LEFT JOIN `scfmforening`
ON `scfmforening`.`scfmnum` = `ml_lopp`.`scfmnum`
LEFT JOIN `ml_tidplats`
ON `ml_tidplats`.`loppnum` = `ml_lopp`.`loppnum`
ORDER BY `thedate`
MySQL is quite forgiving, and will mostly only require you to name the
tables explicitly if there's ambiguity (for instance field1 exists in both
scfmforening & tidplats. Telling it exactly what you want can do no harm
though. You might consider giving your tables an alias (like LEFT JOIN
`scfmforening` 's'), so you don't have to type out the whole tablename
again and again and again.
BTW: SELECT * is no good for production servers unless you're absolutely
sure you need ALL the fields. Your MySQL manual will explain you why in
great detail.
--
Rik Wasmus
Navigation:
[Reply to this message]
|