I'm putting this discussion back on the list so that others can learn from it and/or contribute to it.
So, have I got this right? You have two completely independent and unrelated tables, one containing Phones and one containing Other products and you want to choose a single one at random from the combination of both tables. Your remark about "sql-version up to date" isn't very helpful. Does that mean it is the latest version in the 3.x stream? The 4.0.x stream? The 4.1.x stream? Or the 5.x stream? The version number always appears when you start MySQL, e.g. mysql -u myuserid -p mypswd. Assuming you are using MySQL Version 4.0.x, the obvious solution to your problem is to combine both tables together into a single table via UNION, then choose 1 row from the combined table at random. (There may be better solutions - like choosing a row at random from Phones, choosing a row at random from Others, and then combinining those two rows via UNION and choosing one of them at random - but I'll let others suggest those solutions. And I'll leave it to you to choose the one you like best, based either on simplicity of the solution or performance.) To choose one row at random from a single table, you do this: select * from Phones order by rand() limit 1; If you repeat this query against your table several times, you should find that you usually get a different row each time, although you can choose the same row at random two or three times in a row too. To combine two tables that are not related, as appears to be the case in your situation, you use UNION, not join. The basic look of UNION is this: select * from Table1 UNION select * from Table2 optionally followed by an ORDER BY. The ORDER BY applies to the entire combined result of the UNION, NOT just to the last query. However, UNION has some special rules that have to be observed to use it successfully: the only way the example would work is if Table1 and Table2 had the same number of columns and the columns lined up the same way. For example, if Table1 contained an int, a CHAR, and another int in that order, Table2 would also need to contain a numeric value, a character type column, and another numeric value in that order for the UNION to work. As a result, you don't often use "Select *" in UNIONs because the two tables rarely line up precisely the same way. More often, you will see only specific columns in each select, specifically chosen to line up as I described. Therefore, if Phones contained an integer Product_ID column and Others contained a smallint Part_Number, you'd do this: select Product_ID from Phones UNION select Part_Number from Others If you imitate that reasoning with your UNION, you should get a viable result. Then, add the 'ORDER BY rand() limit 1' to the end of the query and you should be away to the races. For example, I have two "play" tables, one called Employee and the other called emp. To choose one row at random from the UNIONed result, I did this: select empno, lastname from Employee UNION select empno, lastname from emp order by rand() limit 1; It worked perfectly. I don't know when the rand() function and the 'limit' option were added to MySQL so if you are running an oldish version of MySQL, these techniques may not work for you. (I am running MySQL 4.0.15). In that case, please post your version and maybe someone else can suggest something that would work for you. Rhino ----- Original Message ----- From: "Christian Biggins" <[EMAIL PROTECTED]> To: "'Rhino'" <[EMAIL PROTECTED]> Sent: Friday, January 14, 2005 11:43 AM Subject: RE: Selecting a random record from more than 1 table > Hi Rhino, > > Basically, that's exactly what I am trying to do. 2 tables with different > designs and different records and I want to select 1 random record from > either of them. So, I have a phones and a Products table and on the main > page of a site I want a 'random product' box... See what I mean? > > Um, sql version is up-to-date, whichever the latest is. > > Thanks. > > Christian > > -----Original Message----- > From: Rhino [mailto:[EMAIL PROTECTED] > Sent: Saturday, 15 January 2005 12:56 AM > To: Christian Biggins; mysql@lists.mysql.com > Subject: Re: Selecting a random record from more than 1 table > > > ----- Original Message ----- > From: "Christian Biggins" <[EMAIL PROTECTED]> > To: <mysql@lists.mysql.com> > Sent: Friday, January 14, 2005 12:07 AM > Subject: Selecting a random record from more than 1 table > > > > Hi Guys > > > > I am trying to display 1 random record taken from 2 tables. > > > > I have tried the following; > > > > SELECT table1.record1, table1.record2, table2.record1 FROM table1, table2 > > ORDER BY RAND() Limit 1 > > > > With no luck... So now I am trying to use CREATE VIEW but also with no > > luck... Can anybody help out? > > > Your question isn't very clear; I can't make out what you are trying to do. > Can you explain in a bit more detail? > > It sounds like you are trying to do a join of two tables which selects all > records from both which match and then choose one of the resulting records > at random. But, if that is true, your query contains a bad join because it > is actually going to join every row of Table1 with every row of Table2 *even > if they haven nothing in common*. > > I'm not sure why you want to create a view either. > > Also, what version of MySQL are you using? Some things can't be done at all > in some versions of MySQL.... > > Rhino > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
