MySQL query question ...

[ref-2]

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


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

_______________________________________________
Discuss mailing list
Discuss@...
http://lists.blu.org/mailman/listinfo/discuss

[David Kramer]

ref wrote:

> 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 ...

You can do a UNION between two queries that return similar columns
http://www.w3schools.com/sql/sql_union.asp

The other tool you might need to only display records with store_id's
returned from the UNION is INNER JOIN
http://www.w3schools.com/sql/sql_join.asp

Or you can use an "WHERE store_id IN" clause to only return store
information where the store_id's are returned from the UNION.
http://www.w3schools.com/sql/sql_in.asp



--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

_______________________________________________
Discuss mailing list
Discuss@...
http://lists.blu.org/mailman/listinfo/discuss

[Al Wheeler-2]

ref wrote:

> 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
>
>
>  

either of the two tables? something like this, then

and
(
exists ( select 1 from product_table_1 where so_store_id =
store_details.store_id )
or
exists ( select 1 from product_table_2 where so_store_id =
store_details.store_id )
)

note the outer parens

--

Alfred Wheeler


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

_______________________________________________
Discuss mailing list
Discuss@...
http://lists.blu.org/mailman/listinfo/discuss

[David Hummel-2]

On Sun, Jul 6, 2008 at 12:51 PM, ref <tbs@...> wrote:
>
> I want to select contents from "table aay", but only if it has
> relational records in "table bee" OR relational records in "table cee"

I would suggest LEFT JOINing tables bee and cee and checking for
non-NULL key values in the WHERE clause for those tables...

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

_______________________________________________
Discuss mailing list
Discuss@...
http://lists.blu.org/mailman/listinfo/discuss