By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,741 Members | 2,004 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,741 IT Pros & Developers. It's quick & easy.

Concat in a join?

P: n/a
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?

Garry Jones
Sweden

Dec 11 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On 11 Dec 2006 09:22:59 -0800, "GarryJones" <mo****@algonet.sewrote:
>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?
What happened when you tried it?

It looks like you're using MySQL, so the question would be more topical on
comp.databases.mysql.

--
Andy Hassall :: an**@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
Dec 11 '06 #2

P: n/a
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?

Garry Jones
Sweden
Did you already try this and it *didn't* work? Because I just tried a
simple example in MySQL and it worked as expected (the fields concated
regardless of their source table).

Dec 11 '06 #3

P: n/a
Rik
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
Dec 12 '06 #4

P: n/a
Andy Hassall wrote:
...
It looks like you're using MySQL, so the question would be more topical on
comp.databases.mysql.
I can find alt.comp.databases.mysql, but my news provider does
not list comp.databases.mysql

it that correct ?

bill
Dec 13 '06 #5

P: n/a
Rik
bill wrote:
Andy Hassall wrote:
..
> It looks like you're using MySQL, so the question would be more
topical on comp.databases.mysql.

I can find alt.comp.databases.mysql, but my news provider does
not list comp.databases.mysql

it that correct ?
It could be your provider doesn't have it. Both alt.comp.databases.mysql &
comp.databases.mysql exist. alt.comp.databases.mysql is rather quiet
though, not much going on there. You could ask you're provider to start to
carry comp.database.mysql. You could point out that it is better visited
and chances of it actually being usefull are way higher.

Other options:
- look for an accessable server which carries it (there are free news
servers all around)
- google groups (brrrr... only as a last resort. All the restriction of a
ng and all the benefits of an html forum are not a good mix.)

--
Rik Wasmus
Dec 13 '06 #6

P: n/a
Rik wrote:
bill wrote:
>Andy Hassall wrote:
..
>> It looks like you're using MySQL, so the question would be more
topical on comp.databases.mysql.
I can find alt.comp.databases.mysql, but my news provider does
not list comp.databases.mysql

it that correct ?

It could be your provider doesn't have it. Both alt.comp.databases.mysql &
comp.databases.mysql exist. alt.comp.databases.mysql is rather quiet
though, not much going on there. You could ask you're provider to start to
carry comp.database.mysql. You could point out that it is better visited
and chances of it actually being usefull are way higher.

Other options:
- look for an accessable server which carries it (there are free news
servers all around)
- google groups (brrrr... only as a last resort. All the restriction of a
ng and all the benefits of an html forum are not a good mix.)
Thank you
Dec 14 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.