Solved

Alternative to UNION operation

Posted on 2002-06-20
13
805 Views
Last Modified: 2006-11-17
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
0
Comment
Question by:JfSmith
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 2

Expert Comment

by:vasan_sr
ID: 7096712
u can use left join.....only...there is no union adn subquery...
0
 

Author Comment

by:JfSmith
ID: 7096783
And how can I merge two columns into one using a left join?
0
 
LVL 5

Expert Comment

by:kelfink
ID: 7096893
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
 

Author Comment

by:JfSmith
ID: 7096988
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
 
LVL 8

Expert Comment

by:drittich
ID: 7098910
The best thing would be a schema re-design.  But I don't know if that is an option...
0
 

Author Comment

by:JfSmith
ID: 7098952
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 8

Expert Comment

by:drittich
ID: 7098983
>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
 

Author Comment

by:JfSmith
ID: 7099028
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
 
LVL 8

Expert Comment

by:drittich
ID: 7099054
>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
 

Author Comment

by:JfSmith
ID: 7099271
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
 
LVL 8

Expert Comment

by:drittich
ID: 7099296
Sure...

For a db this small, Access would work fine, and would let you use UNION.
0
 
LVL 5

Accepted Solution

by:
kelfink earned 100 total points
ID: 7104953
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
 

Author Comment

by:JfSmith
ID: 7107585
Hehe, I understand. The short-term solution seems best for now. Thanks a lot!

JF
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now