[Resolved] Union Queries

harvillo

I've been away from MySQL for quite a while now and have loaded the latest version which allows UNION queries and I would like to take out some current joins I'm having to do.

I have 2 tables with exactly the same format I would like to run a UNION select on. I visited the mysql site and the syntax provided didn't work properly in my script so I was hoping if someone could point me in the right direction. I'll give an example of the query below in its rawest form. Please excuse the lack of knowledge here.

$sql = mysql_query("SELECT FROM table1 WHERE Type LIKE '$search_string%'") UNION ("SELECT FROM table2 WHERE Type LIKE '$search_string'");

Furthermore, if I were to use LIMIT would I place it in each query? (That is to say if I'm even close to being correct with the syntax above)

Thanks

exactly

sfullman

Here's the manual:

http://www.mysql.com/doc/en/UNION.html

appears the limit is applied to each select. Ergo I don't know if you can limit on the union (which seems counterproductive to the intent of a union statement

The documentation seems spotty at this point, you might also look into ANSI SQL. I'm looking forward to working with the new version 4 myself.

Sam

tomhath

A UNION is all one big SQL query, php doesn'tknow anything about it:

$sql = mysql_query("SELECT * FROM table1 WHERE Type LIKE '$search_string%' UNION SELECT * FROM table2 WHERE Type LIKE '$search_string'");

sfullman

Hi Tom,

Here I think the poster (and myself) were requesting a clarification on the limit clause.

Suppose you have 2 unioned tables and you want to page through the results seamlessly. How do you say "give me LIMIT from n to n on the WHOLE THING" -- because I may not know how many are in each. Something like

(select from table 1 UNION select from table2 ) LIMIT 0, 20

(or 20, 40, or 40, 60, or etc. etc.)

Best,
Sam

harvillo

I thank all of you for the response and it seems as though the last post knows exactly what I'mgoing through with pagination issues. I've changed the query to reflect:

$sql= mysql_query("SELECT FROM table1 WHERE Widget LIKE 'app%' UNION SELECT FROM table2 WHERE Widget LIKE 'app%' ORDER BY Widget DESC LIMIT 0,30");
$results_found = mysql_num_rows($sql);

Something seems to be wrong with my method here as I do have strings in each table with app001,app002 etc and the query is still retrieving no records (i.e. mysql_num_rows=0) though when querying through phpmyadmin all seems to be fine thus operator error must be the problem here.

Thank you again and I do appreciate your response, I understand these newbie issues can br frustrating.

harvillo

Thanks for the help. I was able to tinker and everything seems to be fine.

sfullman

Hi there,

I just had one question, does your query below:

$sql= mysql_query("SELECT FROM table1 WHERE Widget LIKE 'app%' UNION SELECT FROM table2 WHERE Widget LIKE 'app%' ORDER BY Widget DESC LIMIT 0,30");

mean:
1. sort BOTH TABLES by Widget DESC
2. give me the first 30 records from THAT RESULT

???

OR does it mean:
1. give me everything from the first table by critia 'app%'
2. tack on to the end of this the second table, sorting by Widget and then only the first 30.

As you can well imagine, the results would be different.

I'm interested because I will be upgrading to mysql 4.0 in a month and have no way to test.

Sincerely,

Sam Fullman