[wdvltalk] mysql and CONCAT...
Ello all... I've got 2 fields. Depending on the result, one might be NULL, this is intentional. However, if the 2nd IS null, I want to combine it with eh first... eg: field1 | field2 sign up | NULL points claimed | NULL points spent| claimed prize X So in my output I want a new field to be created using CONCAT CONCAT(field1, - , field2) as field1 so I WANT the output of field1 to be: sign up points claimed points spent - claimed prize X but I'm getting a blank field1 if field 2 is null... How can I get round that? -- Give a man a fish and he'll feed himself for a day. Give a man a religion and he'll starve to death praying for a fish. Anon `We are what we pretend to be, so we must be careful what we pretend to be.` Kurt Vonnegut `When a person can no longer laugh at himself, it is time for others to laugh at him.` Thomas Szasz � The WDVL Discussion List from WDVL.COM � To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: wdvltalk@lists.wdvl.com To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk http://www.wdvl.com ___ You are currently subscribed to wdvltalk as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with.
Re: [wdvltalk] mysql and CONCAT...
On Tuesday 21 August 2007 10:54:59 Tris wrote: Ello all... I've got 2 fields. Depending on the result, one might be NULL, this is intentional. However, if the 2nd IS null, I want to combine it with eh first... eg: field1| field2 sign up | NULL points claimed| NULL points spent | claimed prize X So in my output I want a new field to be created using CONCAT CONCAT(field1, - , field2) as field1 so I WANT the output of field1 to be: sign up points claimed points spent - claimed prize X but I'm getting a blank field1 if field 2 is null... select if(field2 is null, field1, concat(field1, ' - ', field2)) ... Something like that ought to do it. (This seems like a very strange DB schema... :) ) -- David Precious :: [EMAIL PROTECTED] http://blog.preshweb.co.uk/ :: http://www.preshweb.co.uk/ � The WDVL Discussion List from WDVL.COM � To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: wdvltalk@lists.wdvl.com To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk http://www.wdvl.com ___ You are currently subscribed to wdvltalk as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with.
Re: [wdvltalk] mysql and CONCAT...
It's a lot of joins...
From user table, points table, rewards table, statement table...
I just simplified it for the post... ;-p
just finishing off
http://www.tlc-rewards.com/4462Dubli/
On 21/08/07, David Precious [EMAIL PROTECTED] wrote:
On Tuesday 21 August 2007 10:54:59 Tris wrote:
Ello all...
I've got 2 fields.
Depending on the result, one might be NULL, this is intentional.
However, if the 2nd IS null, I want to combine it with eh first...
eg:
field1| field2
sign up | NULL
points claimed| NULL
points spent | claimed prize X
So in my output I want a new field to be created using CONCAT
CONCAT(field1, - , field2) as field1
so I WANT the output of field1 to be:
sign up
points claimed
points spent - claimed prize X
but I'm getting a blank field1 if field 2 is null...
select if(field2 is null, field1, concat(field1, ' - ', field2)) ...
Something like that ought to do it.
(This seems like a very strange DB schema... :) )
--
David Precious :: [EMAIL PROTECTED]
http://blog.preshweb.co.uk/ :: http://www.preshweb.co.uk/
• The WDVL Discussion List from WDVL.COM •
To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or
use the web interface http://e-newsletters.internet.com/discussionlists.html/
Send Your Posts To: wdvltalk@lists.wdvl.com
To change subscription settings, add a password or view the web interface:
http://intm-dl.sparklist.com/read/?forum=wdvltalk
http://www.wdvl.com ___
You are currently subscribed to wdvltalk as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
To unsubscribe via postal mail, please contact us at:
Jupitermedia Corp.
Attn: Discussion List Management
475 Park Avenue South
New York, NY 10016
Please include the email address which you have been contacted with.
--
Give a man a fish and he'll feed himself for a day.
Give a man a religion and he'll starve to death praying for a fish.
Anon
`We are what we pretend to be, so we must be careful what we pretend to be.`
Kurt Vonnegut
`When a person can no longer laugh at himself, it is time for others
to laugh at him.`
Thomas Szasz
� The WDVL Discussion List from WDVL.COM �
To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or
use the web interface http://e-newsletters.internet.com/discussionlists.html/
Send Your Posts To: wdvltalk@lists.wdvl.com
To change subscription settings, add a password or view the web interface:
http://intm-dl.sparklist.com/read/?forum=wdvltalk
http://www.wdvl.com ___
You are currently subscribed to $subst('List.Name') as:
$subst('Recip.EmailAddr')
To unsubscribe send a blank email to $subst('Email.UnSub')
To unsubscribe via postal mail, please contact us at:
Jupitermedia Corp.
Attn: Discussion List Management
475 Park Avenue South
New York, NY 10016
Please include the email address which you have been contacted with.
Re: [wdvltalk] mysql and CONCAT...
CONCAT(field1, - , field2) as field1 there are several ways to deal with NULLs here check each field individually -- CONCAT( COALESCE(field1,'') , ' - ' , COALESCE(field2,'') ) however, if field1 is NULL, then the result starts with the dash, so... CONCAT( COALESCE(CONCAT(field1,' - '),'') , COALESCE(field2,'') ) which is esthetically better but rather clumsy my favourite approach -- CONCATE_WS( ' - ' , field1, field2 ) check out CONCAT_WS in the manual for how it works with NULLs ;o) � The WDVL Discussion List from WDVL.COM � To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: wdvltalk@lists.wdvl.com To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk http://www.wdvl.com ___ You are currently subscribed to wdvltalk as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with.
Re: [wdvltalk] mysql and CONCAT...
On Tuesday 21 August 2007 11:53:53 Tris wrote: It's a lot of joins... From user table, points table, rewards table, statement table... I just simplified it for the post... ;-p Ah fair enough :) Did the if(...concat(..)..) work? just finishing off http://www.tlc-rewards.com/4462Dubli/ Cool. When PIPEX were using TLC there were some fairly good rewards on offer (and a fair amount of crap ones, too :) ) -- David Precious :: [EMAIL PROTECTED] http://blog.preshweb.co.uk/ :: http://www.preshweb.co.uk/ � The WDVL Discussion List from WDVL.COM � To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: wdvltalk@lists.wdvl.com To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk http://www.wdvl.com ___ You are currently subscribed to wdvltalk as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with.
Re: [wdvltalk] mysql and CONCAT...
Um, here's my finished query:
SELECT statement.timestamp, CONCAT(transferType.descriptionUser, ,
COALESCE(rewards.title, )) as descriptionUser, statement.calcType,
statement.points
FROM `users`
JOIN statement on
statement.userId = users.id
JOIN transferType on
transferType.id = statement.transferType
LEFT JOIN rewards on
rewards.id = statement.rewardId
WHERE users.id = . $_SESSION[userId] .
ORDER BY timestamp ASC
In the last 18 months, I've really got to grips with Joins etc.. Rudy
helped me through some weird probs... now it's getting easier (note,
stil not easy ;-p )
TLC, yeah, must say nothing... if they paid me on time, they'd be a
great company... ;-p
On 21/08/07, David Precious [EMAIL PROTECTED] wrote:
On Tuesday 21 August 2007 11:53:53 Tris wrote:
It's a lot of joins...
From user table, points table, rewards table, statement table...
I just simplified it for the post... ;-p
Ah fair enough :)
Did the if(...concat(..)..) work?
just finishing off
http://www.tlc-rewards.com/4462Dubli/
Cool. When PIPEX were using TLC there were some fairly good rewards on offer
(and a fair amount of crap ones, too :) )
--
David Precious :: [EMAIL PROTECTED]
http://blog.preshweb.co.uk/ :: http://www.preshweb.co.uk/
• The WDVL Discussion List from WDVL.COM •
To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or
use the web interface http://e-newsletters.internet.com/discussionlists.html/
Send Your Posts To: wdvltalk@lists.wdvl.com
To change subscription settings, add a password or view the web interface:
http://intm-dl.sparklist.com/read/?forum=wdvltalk
http://www.wdvl.com ___
You are currently subscribed to wdvltalk as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
To unsubscribe via postal mail, please contact us at:
Jupitermedia Corp.
Attn: Discussion List Management
475 Park Avenue South
New York, NY 10016
Please include the email address which you have been contacted with.
--
Give a man a fish and he'll feed himself for a day.
Give a man a religion and he'll starve to death praying for a fish.
Anon
`We are what we pretend to be, so we must be careful what we pretend to be.`
Kurt Vonnegut
`When a person can no longer laugh at himself, it is time for others
to laugh at him.`
Thomas Szasz
� The WDVL Discussion List from WDVL.COM �
To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or
use the web interface http://e-newsletters.internet.com/discussionlists.html/
Send Your Posts To: wdvltalk@lists.wdvl.com
To change subscription settings, add a password or view the web interface:
http://intm-dl.sparklist.com/read/?forum=wdvltalk
http://www.wdvl.com ___
You are currently subscribed to $subst('List.Name') as:
$subst('Recip.EmailAddr')
To unsubscribe send a blank email to $subst('Email.UnSub')
To unsubscribe via postal mail, please contact us at:
Jupitermedia Corp.
Attn: Discussion List Management
475 Park Avenue South
New York, NY 10016
Please include the email address which you have been contacted with.
