Re:MySQL query question ...

["markw-4" <user=5@nabble.blu.org>]

 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