[RESOLVED] Conditional Join

edraynham

Hi Folks,

I've got a "products" table and also a "special_prices" table in a commerce senario.

The products table holds product information with prod_id as the primary key. The special_prices table may contain a special price (discount) for that product. The special_prices table also contains a start stamp (DATE) and an end stamp (DATE).

I want to extract the product and special price ( if any) that is valid for the date range...

for example...

The query to extract the product and (maybe) the special price looks like..

SELECT *
FROM products
INNER JOIN specials USING(prod_id)
WHERE products.prod_id = $prodID
AND prod_enabled = 'y'
LIMIT 1

however, i want to add the date condition... so that's something like.....

SELECT *
FROM products
INNER JOIN specials USING(prod_id)
WHERE products.prod_id = $prodID
AND prod_enabled = 'y'
AND ( specials.special_start <= NOW() AND specials.special_end > NOW() )
LIMIT 1

But if there is no special price within this date range then the query returns 0 results (not even the product)

Any ideas?

Thanks

edraynham

It's worth noting that i'm using LEFT JOIN not INNER

mrhappiness

SELECT *
FROM products
  LEFT JOIN specials 
    ON 
      products.prod_id = specials.prod_id 
      AND (specials.special_start <= NOW() AND specials.special_end > NOW()) 
WHERE 
  products.prod_id = $prodID
  AND prod_enabled = 'y'

LoganK

mrhappiness, just what is the guidelines for formatting SQL statements? Around this site a few times I've seen people post long SQL queries like yours, and formatted with certain operators on new lines or indented, etc. What is the guidelines for something like that?

...or am I just off my rocker?

Drakla

Formatting isn't set in stone, but the good thing about it is that you can get error messages from a query telling you what line it's on e.g

EXPLAIN select terms from users
WHERE terms > 0
LIMIT 20
QUACK QUACK;

Gives: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'QUACK QUACK' at line 4

LoganK

Hmmm...never thought of it that way. I never realized that SQL is a language in and of itself, with support for columns, line numbers, etc.

Thanks!