* [EMAIL PROTECTED] > I've been a member of this mailing list for quite a long time and very > thankful that most of my difficulty in creating queries are being answered > by just reading responses to questions. Right now I have a problem in > creating a query (in fact I'm not sure if this is possible) that will get > data from two tables and display its row from one table followed by the > same row number of the other table.
"...same row number..."? There is no such thing as a "row number" in mysql, unless you have an integer column you can use for this, like an "id" field. (The mysql "_rowid" is just an alias for any existing integer primary/unique key.) It is however possible to do it without such a column, using multiple statements, a temporary table and a user variable. See below. > Table1 > col_1 col_2 col_3 col_4 > A A A A > A A A A > A A A A > A A A A > A A A A > A A A A > A A A A > . > . > . > > Table2 > col_1 col_2 col_3 col_4 > B B B B > B B B B > B B B B > B B B B > B B B B > B B B B > B B B B > . > . > . > > Query result > col_1 col_2 col_3 col_4 > A A A A > B B B B > A A A A > B B B B > A A A A > B B B B > A A A A > . > . > . > > I will really appreciate any of your help in accomplishing this. OK, here we go: set @a:=0; create temporary table tmp1 select *,@a:=@a+1 id,'1' tabno from Table1; set @a:=0; insert into tmp1 select *,@a:=@a+1 id,'2' tabno from Table2; select * from tmp1 order by id,tabno; Using a user variable (@a) as a counter, we get the wanted "row number". This is saved in the temporary table, and later used for the sorting. The 'tabno' column is used to sort all items from Table1 before items from Table2. The values '1' and '2' could be any values giving the correct sort order, like 'a'/'b' or 'tab1'/'tab2'. Read about user variables: <URL: http://www.mysql.com/doc/en/Variables.html > <URL: http://www.mysql.com/doc/en/example-user-variables.html > Beware that this is not replication safe, as user variables are not (yet) correctly replicated in mysql: <URL: http://www.mysql.com/doc/en/Replication_Features.html > If you use a newer version of mysql (4.0 or later), you may be able to use UNION instead of the temporary table: <URL: http://www.mysql.com/doc/en/UNION.html > HTH, -- Roger --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
