Mysql "IF" in query

Question

I have few tables

table201202
table201203
table201204
table201205  
...
tableversion

In tableversion I have columns: Version_ID Admin_ID MONTH YEAR

I need to get something like this

Select *
FROM tableversion
LEFT JOIN table(tableversion.month tableversion.year) on
    table(tableversion.month tableversion.year).Version_ID=tableversion.version_id

But in tableversion I've got months without leading 0 so i must check if there is 5 and then change it to 05, if variable is 12 then nothing. How can I do it?

Ok, so now i have something like this

SELECT *, CONCAT('table',IF(tableversion.month < 10, CONCAT('0', tableversion.month ), tableversion.month ),year) as year
FROM tableversion
LEFT JOIN year ON tableversion.version_id=year.version_id WHERE ADMIN_ID=11

#1146 - Table 'database.year' doesn't exist

But it does not work

@AustinHenley if you know how can be done make a post with a sample :) — Luiggi Mendoza
– Luiggi Mendoza, Commented May 7, 2012 at 5:22

Community · Accepted Answer · 2017-05-23 12:04:51Z

3

As seen here, use LPAD:

SELECT LPAD(month,2,'0') ...

edited May 23, 2017 at 12:04

CommunityBot

11 silver badge

answered May 7, 2012 at 5:22

Jonathan Hall

80.5k19 gold badges162 silver badges206 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bryan · Accepted Answer · 2012-05-07 05:19:55Z

1

Try this out in the area where you're concerned about comparing the months

IF(tableversion.month < 10, CONCAT('0', tableversion.month), tableversion.month)

answered May 7, 2012 at 5:19

Bryan

6,7522 gold badges19 silver badges21 bronze badges

8 Comments

breq Over a year ago

ok, what is wrong here? SELECT * FROM tableversion JOIN CONCAT_WS('table.',IF(tableversion.month< 10, CONCAT('0', tableversion.month), tableversion.month),'tableversion.year') ON CONCAT_WS('grafik.',IF(tableversion.month < 10, CONCAT('0', tableversion.month), tableversion.month),'tableversion.year').version_id=tableversion.version_id WHERE ADMIN_ID =11 LIMIT 10

Bryan Over a year ago

Your initial CONCAT_WS is using table., shouldn't it be tableversion. ? Also, your original query is using table(, not table., maybe that's where I'm confused.

Jonathan Hall Over a year ago

@breq: That should probably be another question... but... HOW does it not work?

Bryan Over a year ago

It's indeed a very concerning query, I've never seen anything implemented the way he's doing it, but if he got it working before, I suppose using CONCAT_WS is not a far stretch

breq Over a year ago

@Bryan Moyles, no, it must be "table." because i need to join * from for example table201204 so i need to use CONCAT_WS('table',tableversion.month,tableversion.year) :>

|

Mahmoud Al-Qudsi · Accepted Answer · 2012-05-07 05:22:41Z

1

I think an IF statement would likely perform poorly, but I have no data to back that claim.

You can try this alternative to an IF statement to achieve the same: SELECT *, LPAD(month, 2, '0') as month2 ....

answered May 7, 2012 at 5:22

Mahmoud Al-Qudsi

29.8k13 gold badges94 silver badges130 bronze badges

Collectives™ on Stack Overflow

Mysql "IF" in query

3 Answers 3

Comments

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related