Alternative to UNION operation

I'm using the latest stable  - and recommended - release of MySQL, the 3.23 version. What bugs me is that the UNION operation is only implemented in MySQL 4.0 (as per http://www.mysql.com/doc/U/N/UNION.html), while this one is still alpha. I'm not too sure about using it, especially just to accomodate ONE query. Are there any alternatives? Anything that might give the same result as UNION? Bascially, what I need is a query that merges two columns into one.

Thanks,
Jeff
JfSmithAsked:
Who is Participating?
 
kelfinkConnect With a Mentor Commented:
Sorry for the delay.  Had a spot of time off.

Well, I was hoping that some form of join would accomplish what you want.  But without redesigning, I don't see it.
drittich's suggestion to redesign the schema is essentially a more organized method of accomplishing what I suggested.  Long term, his idea should be best for organization.  Short term, my TEMP table should get the job done, and minimize other changes necessary in the code.

If you're happy with the structure for now, and want to wait for stable 4.0 to support UNION (I'd avoid implementing the beta 4.0 outside of a test environment...)
then my TEMP solution is minimal work. Here's the steps, roughly...

1) beforehand, create table TEMPAUTH ( authid, name );
2) INSERT INTO TEMPAUTH (authid, name) SELECT authid1, name1 from BOOKS;
3) INSERT INTO TEMPAUTH (authid, name) SELECT authid2, name2 from BOOKS;
4) SELECT AUTHID, NAME FROM TEMPAUTH;
5) TRUNCATE TABLE TEMPAUTH;

Those steps assume you've got all your author data in the book table.  I'm guessing if you had a author table we wouldn't be having his discussion?

In a more pure world, you'd have one author table, which you'd join to the books table.  The author table could contain all kinds of wonderful information about the author, so you can keep that information in one place, easily modified... current phone number, address, university, etc..  That's normalization.  It's great.












0
 
vasan_srCommented:
u can use left join.....only...there is no union adn subquery...
0
 
JfSmithAuthor Commented:
And how can I merge two columns into one using a left join?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
kelfinkCommented:
I'm sure we'd have to see more of what you're trying to do as a UNION, in order to recommend a substitution.

THe standard replacement for UNION in mySQL is to use a temporary table, and use each select in the union separately to insert into the temp.  Then select DISTINCT records (for standard UNION) to get the rows back.

That's a lot of work for the one query, though.  If you post more details, a more elegant answer might be available.

As for merging two columns, a variety of mySQL functions can test the value of a column in a result set, and replace it with another column:  ifnull, isnull, case, etc...



0
 
JfSmithAuthor Commented:
Kelfink, thanks for your reply.

What I'm doing is rather straightforward. Here I'll adapt and simplify things a little bit. Let's see. There's a table, called SOURCES, with various information about numerous published sources. It has two author fields, say author1 and author2, for each record. For the purpose of building a drop-down list containing ALL authors ever added in the whole table SOURCES, I need to merge all the unique entries of the fields author1 and author2 into a one-column query.

I actually used MSSQL and a UNION operator, and everything was fine. But now I have migrated to MySQL. Please convince me everything's fine with that. ;-)

By the way, for me it is important to do it that way, because the drop-down list is built dynamically. Maintaining a separate and static index of authors would work but would be too much pain.

Also, perhaps you can tell me what are the "risks" to use MySQL 4.0 alpha. This way I wouldn't have to readap my query.

Thanks a lot,
Jeff
0
 
drittichCommented:
The best thing would be a schema re-design.  But I don't know if that is an option...
0
 
JfSmithAuthor Commented:
Unfortunately I cannot re-design the structure of the table. The two author fields have to be kept separate for several reasons, but here especially because they must be listed *individually* in my drop-down list.

I really need a way to figure this one out... I repeat I COULD do that with MSSQL. I really didn't expect MySQL to disappoint me on that... unless I use MySQl 4.0. Can somebody tell me if that is risky or not?

Jeff
0
 
drittichCommented:
>Unfortunately I cannot re-design the structure of the
>table. The two author fields have to be kept separate for
>several reasons, but here especially because they must be
>listed *individually* in my drop-down list.

You can do that with a new schema...

I have not used MySQL 4, so can't recommend.  Is this for a web app?  Shouldn't be a problem to handle this in code rather than SQL...

0
 
JfSmithAuthor Commented:
Well, I can't shy away from my limited experience, so I have to ask what is a schema.

It is indeed for a webapp. I agree I could code something, but it seems so much out of proportions for just one query, and to replace ONE word at that. :-) I'm still under the shock that this detail turns out to be such an issue. I'd have to grab the results of two separate SELECT DISTINCT queries, and then analyse everything so that every entry gets listed only once. Ouch. Now our table has 4366 records, meant to merge with similar projects by other teams. It's meant to get bigger.

Jeff
0
 
drittichCommented:
>I'd have to grab the results of two separate SELECT
>DISTINCT queries, and then analyse everything so that
>every entry gets listed only once.

No, you would use kelfink's method (temp table), and give him the points...

The schema is the layout of your db - what tables, columns are there.  Your current schema is de-normalized, that is why you are having problems like this.

Instead of TWO author fields in the SOURCES table, have none.  Then create an AUTHORS table, and a SOURCES_AUTHORS table. SOURCES_AUTHORS would contain

SOURCE_ID  AUTHOR_ID
1               27
1              995
2             1732
2             8457

So each author entry for a source gets a row in the SOURCES_AUTHORS table.  This has additional benefits, e.g., you can have as many authors for a source as you want.

That way you would just do

SELECT * FROM AUTHORS to get your list - make the table unique by definition.  Or if you can't guarantee uniqueness, you would do SELECT DISTINCT LAST_NAME, FIRST_NAME FROM AUTHORS
0
 
JfSmithAuthor Commented:
Oh, schema's a simple word after all. Thank you for that.
Well, this is a very good solution that I realize now to have actually applied elsewhere (I'm not the original designer of the database currently under my administration). But in this case it is a little more complicated. It's just the situation. I really don't need to go into details though, you'd be bored to death quickly. This particular MySQL table is part of a database that is in truth a web version of an original FileMaker Pro database, which is under the responsibility of another team. The two versions need to be kept consistent, and I cannot impose the schema you suggest, however efficient, to the FileMaker side of the project.

If you don't mind, I'll wait a little more and see if kelfink has some more insights.

Thanks,
Jeff
0
 
drittichCommented:
Sure...

For a db this small, Access would work fine, and would let you use UNION.
0
 
JfSmithAuthor Commented:
Hehe, I understand. The short-term solution seems best for now. Thanks a lot!

JF
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.