Home
| Calendar
| Mail Lists
| List Archives
| Desktop SIG
| Hardware Hacking SIG
Wiki | Flickr | PicasaWeb | Video | Maps & Directions | Installfests | Keysignings Linux Cafe | Meeting Notes | Blog | Linux Links | Bling | About BLU |
I'm not a big fan of MySQL because it doesn't really support SQL well enough to do proper queries, but here are some things to try. When you have a complex query, it is best to break it down to the logical elements of the data you want to retrieve. In your case, you want a store ID from one or more tables, this is typically a "union" sort of construct. A union is basically multiple queries who's results are stored as one. select store_id from product_table_one where product = 'microwave' union select store_id from product_table_two where product = 'microwave' That's should get you the stores. if any. Then with that as a clause, select distinct * from (...) order by store_id - That should remove the duplicates and order correctly. Then: select * from store_details, (select distinct * from (your store union clause from above)) as foo where store_details. store_id = foo.store_id; Whether or not MySQL is capable of doing this or not probably depends on your version. Be careful of the "where in" clause I read someone mentioned as MySQL has a very poor pathological failure in performance. > Date: Sun, 06 Jul 2008 12:51:38 -0400 > From: ref <[hidden email]> > Subject: MySQL query question ... > To: L-blu <[hidden email]> > Message-ID: <[hidden email]> > Content-Type: text/plain > > Hi folks, > dont know if this is the correct place for this, but I was wondering if > there are any MySQL gurus out there who could help me with this. > > Problem : > > I want to select contents from "table aay", but only if it has > relational records in "table bee" OR relational records in "table cee" > > Rationale : > > I have a store table, called "store_details". I want to be able to pick > a random store from the store details table. However, as I dont want to > display an empty or unready store, so I want to check and see if that > store has items in it. These items can be stored in either of two > tables, depending on the product being sold. > Both product_table_two and product_table_one have the store_id key from > store_details. > So, in part-pseudo code it would look like : > > SELECT * FROM > store_details , > product_table_one, > product_table_two, > WHERE store_id >= (SELECT FLOOR( MAX(store_id) * RAND()) FROM > page_details ) > and > BOTH (product_table_one AND Product_table_two) are NOT EMPTY ... > ORDER BY so_store_id LIMIT 1 > > I just dont see how to get the 'either or' part going ... > > Can anyone help me ? > > thank you in advance, > > Richard
BLU is a member of BostonUserGroups | |
We also thank MIT for the use of their facilities. |