MySQL

Using Joins and Keys

[Alternative Future]

Hello to the forum,

MySQL version 4.0.23-standard

I have a TABLE named widget_ads with the columns TYPE, AD1, AD2 - AD7 each column named AD# has a unique product id.
My other TABLE is named widgets with the columns ID, COLOR, PRICE.

I want to query table widget_ads and retrieve all AD#'s WHERE widget_ads.TYPE = myQuery
Then get all widgets from TABLE widgets WHERE ID = widget_ads.ID returned from the first query.

Could someone please help me with the proper query...?

TIA,

-George

[txbakers]

First make a query to get the type you want.

rsAdType.Source = "select fields from widget_ads where type =" + myQueryCode

then, once you have that, make a second query using the result from the first query as a where clause.

[Alternative Future]

Hi txbakers,

I am currently using my dispatchAction to get the first query and then sending that back to get the results from the second table. Is that what you mean? I just thought there might have been a more efficent way of doing the query all at once from a MySql command.
Many thanks for answering my question...

-George

[Alternative Future]

Will this work when the results from the first query is an array list I mean more than one ID?

-George

[coopster]

Since you are on MySQL >= 4.0.0 you could go with a UNION [dev.mysql.com]:

--  
-- MySQL >= 4.0 
--  
(SELECT ID, COLOR, PRICE FROM widgets INNER JOIN widgets_ads ON (widgets.ID = widgets_ads.AD1) WHERE widgets_ads.TYPE = '$myQuery') 
UNION 
(SELECT ID, COLOR, PRICE FROM widgets INNER JOIN widgets_ads ON (widgets.ID = widgets_ads.AD2) WHERE widgets_ads.TYPE = '$myQuery') 
UNION 
(SELECT ID, COLOR, PRICE FROM widgets INNER JOIN widgets_ads ON (widgets.ID = widgets_ads.AD3) WHERE widgets_ads.TYPE = '$myQuery') 
UNION 
(SELECT ID, COLOR, PRICE FROM widgets INNER JOIN widgets_ads ON (widgets.ID = widgets_ads.AD4) WHERE widgets_ads.TYPE = '$myQuery') 
UNION 
(SELECT ID, COLOR, PRICE FROM widgets INNER JOIN widgets_ads ON (widgets.ID = widgets_ads.AD5) WHERE widgets_ads.TYPE = '$myQuery') 
UNION 
(SELECT ID, COLOR, PRICE FROM widgets INNER JOIN widgets_ads ON (widgets.ID = widgets_ads.AD6) WHERE widgets_ads.TYPE = '$myQuery') 
UNION 
(SELECT ID, COLOR, PRICE FROM widgets INNER JOIN widgets_ads ON (widgets.ID = widgets_ads.AD7) WHERE widgets_ads.TYPE = '$myQuery') 
;

---

Another option for those on MySQL >= 4.1

--  
-- MySQL >= 4.1 
-- [dev.mysql.com...]  
--  
SELECT 
 ID, COLOR, PRICE 
 FROM widgets 
 WHERE EXISTS 
 (SELECT * FROM widgets_ads 
   WHERE 
   widgets_ads.TYPE = '$myQuery' AND 
   (widgets.ID = widgets_ads.AD1 OR 
   widgets.ID = widgets_ads.AD2 OR 
   widgets.ID = widgets_ads.AD3 OR 
   widgets.ID = widgets_ads.AD4 OR 
   widgets.ID = widgets_ads.AD5 OR 
   widgets.ID = widgets_ads.AD6 OR 
   widgets.ID = widgets_ads.AD7) 
 ) 
;

[Alternative Future]

Thanks guyz,

Nice to get a few options to play around with its appreciated...

-George

[Alternative Future]

Hi coopster,

With option one, we are repeating each SELECT statement with a JOIN up to and including the 7th query. Will this cause an issue if for instance my widget_ads TABLE only had 3 AD#'s WHERE TYPE = myQuery?

TIA,

-george

[txbakers]

No, the UNION will bring in everything it can and the other select statements won't bring in anything.

UNIONs are a good way to bring similar data from multiple tables without the ability to join.

The nested SELECT (as part of the where clause) is a good idea also, which can save some processor time. I prefer the two-step method just because I usually do other things with the first query and can quickly decipher my intentions when I need to revisit that page.

[Alternative Future]

Hi txbakers,

Like you I prefer the second option with the nested select, but at present I am limited to the version I stated above as this is what my current hosts provide.

>>No, the UNION will bring in everything it can and the other select statements won't bring in anything.

So will this result in an error or would I be ok to use this even though sometimes I will not have 7 queries?

TIA,

-George

[coopster]

How can you not have 7 queries? If your table is structured as stated earlier ...

widget_ads

TYPE 
AD1 
AD2 
AD3 
AD4 
AD5 
AD6 
AD7

...you will always have to check each of the seven 'AD' columns for the TYPE='searchterm' query, will you not? Did I miss something?

[Alternative Future]

Hi Coopster,

>you will always have to check each of the seven 'AD' columns for the TYPE='searchterm' query, will you not? Did I miss something?
No you have it correct, what I mean to ask is sometimes the AD# will not have a value for that queryString will that just return null or cause an error?

TIA,

-George

[coopster]

Did you test it?

It would be as txbakers stated:

the other select statements won't bring in anything.

;)

[Alternative Future]

Ahh ok,

Not had a chance to implement it yet as I was at work, will do as soon as I get home.

Thanks to both of you

-George