Mysql Query to return 2 columns as 1

captainmerton · 07-19-2009, 06:25 PM

I am looking to return 2 columns from 2 different tables as 1 column - is this possible?

eh.

Table1
ID DATA1
10 blahblah
15 blahblah

Table2
ID DATA2
20 blahblah

Say I want to write a query that will return 10,15,20 how would I do this assuming ID is declared the same on both tables?

captainmerton · 07-19-2009, 06:30 PM

Please ignore - I just discovered the UNION command.

JaoudeStudios · 07-20-2009, 04:35 AM

UNION is very slow as it uses temporary tables. Using a JOIN would be faster.

captainmerton · 07-20-2009, 10:55 AM

Any examples of how I can achieve the 1 merged column using a JOIN? i've used a UNION but am concious of performance so would prefer to use a JOIN.

JaoudeStudios · 07-20-2009, 12:12 PM

Example
SELECT Data1,Data2
FROM Table1
JOIN Table2 ON Table1.id = Table2.id

I have just noticed you do not have the same ID in both tables???
QUOTE "declared (ID) the same on both tables"

Are the IDs in both tables foreign keys?

captainmerton · 07-20-2009, 12:40 PM

What you have written will simply return rows in the format:

data1,data2
data1,data2

Using my example say the column names on either table are different i.e.

Table1
ID1 DATA1
10 blahblah
15 blahblah

Table2
ID2 DATA2
20 blahblah

I want a way of returning the data like this:

ID1
ID1
ID2
ID1

as opposed to this:

ID1,ID2
ID1,ID2

Both ID columns are declared the same eg CHAR(2).

I can get what i want using a UNION but cant find a join to do it. I really dont think your basic join example will provide what i'm looking for needs further honed.

JaoudeStudios · 07-20-2009, 12:54 PM

Sorry I miss understood what you were trying to achieve.

captainmerton · 07-20-2009, 01:13 PM

Still - any idea whether what i want to achieve can be done with a JOIN? I'm no database expert but just looking at the UNION I have doing the job just now I suspect it isnt the most efficient of solutions.

JaoudeStudios · 07-20-2009, 01:53 PM

As far as I know you would need a UNION.