?
Solved

MySQL Select multiple columns as one

Posted on 2004-11-22
6
Medium Priority
?
983 Views
Last Modified: 2008-01-09
Hi, I have a table with the following fields in:

image1
image2
image3

I want to select them all as one column and loop through them all populating a single listbox with them.

I tried combining them all using syntax like

"SELECT (image1, image2, image3) AS image FROM tblimages"

but this didn't work, it just gave me an error. I also tried:

"SELECT image (image1, image2, image3) FROM tblimages"

but this also gave me an error. Lastly I tried

"SELECT image1 AS image, image AS image, image3 AS image FROM tblimages"

This didn't give me an error but it stil returned 3 seperate columns but all with the same name.

I know this is going to be really simple isn't it! Please help!
0
Comment
Question by:m175400
  • 3
  • 2
6 Comments
 
LVL 48

Expert Comment

by:hernst42
ID: 12646049
what do you mean by one column? You can use concat.

SELECT  concat(image1, image2, image3) AS image FROM tblimages

If you want to get those three columns as three seperate rows you will need multiple joins and if -conditions in your query and the query will be come very complicated.
0
 
LVL 1

Author Comment

by:m175400
ID: 12646120
I want all the columns to be returns as if they're just one column, one record after the other. I don't want to actually combine the values into new values, I want all the records from all the columns and to be able to page through all the rows sequentially just as if I'd done

SELECT image1 FROM tblImages

Make sense now?

Thanks!
0
 
LVL 48

Expert Comment

by:hernst42
ID: 12646319
If you want to do that, The only solution I can think of is having a second table (ctable) only a id-column with three rows where id is 1,2 and 3
CREATE TABLE ctable (id int);
insert INTO ctable VALUES (1), (2), (3);

Then use this select:
Select if(b.id=1, image1,if(b.id=2, image2, image3)) FROM  tblimages a,  ctable b

Ther migth be a solution to do it with one tbale, but that is very complicated and beyond my knowledge

0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 20

Accepted Solution

by:
virmaior earned 500 total points
ID: 12647981
or alternately use the union syntax:

( SELECT image1 as image FROM tblimages)
UNION
(SELECT image2 as image FROM tblimages)
UNION
(SELECT image3 as image FROM tblimages)

(which is the right way of saying: SELECT image1 AS image, image AS image, image3 AS image FROM tblimages)

problematically this will require a version of MySQL of 4.0+
0
 
LVL 1

Author Comment

by:m175400
ID: 12649734
Many thanks! That seems to work! One weird thing though - I get one extra record with a null value retrieved? Any ideas?
0
 
LVL 1

Author Comment

by:m175400
ID: 12649752
Ah nevermind, sorted it.

Thanks again guys!
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month15 days, 13 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question