473,544 Members | 1,738 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Concat in a join?

I think the following statement ....

$ml_collect='SE LECT *, DATE(CONCAT(fie ld1, field2)) AS thedate FROM
ml_lopp LEFT JOIN scfmforening ON (scfmforening.s cfmnum =
ml_lopp.scfmnum ) LEFT JOIN ml_tidplats ON (ml_tidplats.lo ppnum =
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_tidplat s" and
accessed by "LEFT JOIN".

So, how do I use "DATE(CONCAT(fi eld1, field2))" in this case?

Garry Jones
Sweden

Dec 11 '06 #1
6 1896
On 11 Dec 2006 09:22:59 -0800, "GarryJones " <mo****@algonet .sewrote:
>I think the following statement ....

$ml_collect='S ELECT *, DATE(CONCAT(fie ld1, field2)) AS thedate FROM
ml_lopp LEFT JOIN scfmforening ON (scfmforening.s cfmnum =
ml_lopp.scfmnu m) LEFT JOIN ml_tidplats ON (ml_tidplats.lo ppnum =
ml_lopp.loppnu m) ORDER BY thedate';

....would work if "field1" and "field2" were in the table "ml_lopp"

However, "field1" and "field2" are in the table "ml_tidplat s" and
accessed by "LEFT JOIN".

So, how do I use "DATE(CONCAT(fi eld1, 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.u k :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
Dec 11 '06 #2
GarryJones wrote:
I think the following statement ....

$ml_collect='SE LECT *, DATE(CONCAT(fie ld1, field2)) AS thedate FROM
ml_lopp LEFT JOIN scfmforening ON (scfmforening.s cfmnum =
ml_lopp.scfmnum ) LEFT JOIN ml_tidplats ON (ml_tidplats.lo ppnum =
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_tidplat s" and
accessed by "LEFT JOIN".

So, how do I use "DATE(CONCAT(fi eld1, 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
Rik
GarryJones wrote:
I think the following statement ....

$ml_collect='SE LECT *, DATE(CONCAT(fie ld1, field2)) AS thedate FROM
ml_lopp LEFT JOIN scfmforening ON (scfmforening.s cfmnum =
ml_lopp.scfmnum ) LEFT JOIN ml_tidplats ON (ml_tidplats.lo ppnum =
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_tidplat s" and
accessed by "LEFT JOIN".

So, how do I use "DATE(CONCAT(fi eld1, field2))" in this case?
When all else fails, try to get them explicitly:
SELECT
`ml_lopp`.*,
DATE(CONCAT(`ml _tidplats`.`fie ld1`, `ml_tidplats`.` field2`)) AS
'thedate'
FROM `ml_lopp`
LEFT JOIN `scfmforening`
ON `scfmforening`. `scfmnum` = `ml_lopp`.`scfm num`
LEFT JOIN `ml_tidplats`
ON `ml_tidplats`.` loppnum` = `ml_lopp`.`lopp num`
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
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.databa ses.mysql, but my news provider does
not list comp.databases. mysql

it that correct ?

bill
Dec 13 '06 #5
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.databa ses.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.databa ses.mysql &
comp.databases. mysql exist. alt.comp.databa ses.mysql is rather quiet
though, not much going on there. You could ask you're provider to start to
carry comp.database.m ysql. 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
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.databa ses.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.databa ses.mysql &
comp.databases. mysql exist. alt.comp.databa ses.mysql is rather quiet
though, not much going on there. You could ask you're provider to start to
carry comp.database.m ysql. 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
1823
by: F. Da Costa | last post by:
Hi, Could it be correct that the following code does *not* work because i'm not using the var arr = new Array("a","b","c"); methodology?? Read through http://devedge.netscape.com/library/manuals/2000/javascript/1.5/reference/array.html#1194827 but there was no mention of particular constructors having to be used. And if so how does one...
4
20780
by: Martin Evans | last post by:
Hi, I'm getting: DBD::DB2::db do failed: SQL0440N No authorized routine named "CONCAT" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884 for some SQL like this:
4
1431
by: s99999999s2003 | last post by:
hi what is the python way to concat 2 lines eg line 1 with some text line 2 with some text i want to bring line 2 up , such that i get one whole string. line 1 with some text line 2 with some text
8
15881
by: Hercules Dev. | last post by:
Hi all, I'm new in xslt and xpath, so my question might be simple but i'm learning. I have an XML document and need to transform it into another XML, I use xslt and it works, but there is a case that i don't know how to solve, I need to concat a string from multiple childs into a standard way, the following is an example of the source...
18
5895
by: NoWhereMan | last post by:
Maybe a stupid question. ------------------------- 1 ------------------------- $str = ''; for($i=0; $i<10; $i++) $str .= $i;
6
26819
by: antmail | last post by:
Hi guys, I have spend several days now trying to solve this problem inside a stored procedure. I want to call a procedure providing 3 variables. The variables area used to select the appropriate table from within the database. I have to concatenate the 3 variables to make up the whole table name and concatenate that with the field name. ...
1
6578
by: Sharat Koya | last post by:
I have a the following xml node. <doc tag1="a" tag2="b" tag3="c" docTag1="d" docTag2="e"/> I would like to output "abc" I have the following XPATH2.0 so far //doc/@*] which returns a node list. Note that docTag* are ignored and there could be any number of tag*.
2
2407
Claus Mygind
by: Claus Mygind | last post by:
Is it possible to conduct a test in the select statement to see if a value is not found in one table (t2) then get the value from another table (t3). In this case you would have 3 tables. if there is no related record in table2 t2, then get the value from table3 t3 If so what would the code look like? select t1.*, concat( ) as...
3
5207
by: shanmugamit | last post by:
HI, i need solution for dynamic query in stored procedure without concat. How to create dynamic query like pagination passing limit value. IF category='artist' THEN IF lim=0 THEN SET @qry = CONCAT('SELECT distinct(fld_art_img_id) AS thumb_id, fld_art_img_file_name AS thumb_img, fld_mas_art_name AS thumb_title, fld_mas_prof_name AS...
0
7414
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7598
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7757
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7699
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5288
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3400
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3398
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1833
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
651
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.