Query Help

[Anon]

Hi,
I need help with buliding a query from following two tables.

TABLE biz

| bizid | int(9) |
| bizname | varchar(50) |

| state | tinyint(2) |

TABLE coupons

| couponid | int(9) |
| bizid | int(9) |

| couponType | enum('O','P') |

Results I want to get are..if someone tries to find coupons by specifying
state then query should return all the businesses from biz table that matches
that state and biz.bizid=coupons.bizid AND it should return all the coupons
which has couponType=P even if it does not match state in biz table.

Hope my question is clear
Thanks for the help in advance.

[Anon]

Ok, there are two things we need to do. First, we need to talk about joins and how they are used to connect tables with related elements. In your case we would do:

SELECT * FROM biz LEFT JOIN coupons ON biz.bizid = coupons.bizid;

This would give us a list of all the coupons with the related business info attached. Next, you want to limit your query with the WHERE clause, to only pull out what the user wants. Now we would do:

SELECT * FROM biz LEFT JOIN coupons ON biz.bizid = coupons.bizid WHERE (biz.state = '$state') OR (coupon.couponType = 'P');

This statement merely says 'give me rows with the coupon information as well as the businesses information, but only if the state is equal to the variable $state (assuming the state the user is querying on is in the variable $state) OR the coupon type is equal to P.

Hope that helps!

Chris King

[jordan]

I don't think if there's any reason to mess around with JOINs when the following, I think, will work:

SELECT biz.bizid, biz.bizname, coupons.couponid, coupons.couponType FROM biz, coupons WHERE ((biz.state = '$state') && (biz.bizid = coupons.bizid)) || couponType = 'P';