473,700 Members | 2,725 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 1902
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
1835
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 resolve it (if resolvable)?
4
20911
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
1435
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
15896
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 and the target XML.
18
5918
by: NoWhereMan | last post by:
Maybe a stupid question. ------------------------- 1 ------------------------- $str = ''; for($i=0; $i<10; $i++) $str .= $i;
6
26848
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. The hard coded procedures looks as follows: DROP PROCEDURE IF EXISTS...
1
6615
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
2413
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 aFoundValue from table1 t1
3
5212
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 profession,fld_art_img_description AS gal_des,fld_mas_art_id as rel_id,fld_gal_art_p_link as...
0
8709
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 usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8638
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9058
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 captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8952
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7791
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5894
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4395
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4649
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3081
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

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.