Query multiple fields in sql query using same variable

Question

I have the query below which works great.

    SELECT  `Despatch`.`DespatchNumber` , `Despatch`.`Product` ,
           `Stock`.`ProductNumber` ,  `Stock`.`Description` ,      
     `Stock`.`ProductGroup` ,  `Stock`.`Size` ,  `Stock`.`IPICODE`  , `Stock`.`Fit` ,  
     `Stock`.`62` 
            FROM Stock , Despatch 
            WHERE  `Despatch`.`DespatchNumber` = '" . $Despatch . "'   
            AND   `Despatch`.`Product`  =    `Stock`.`ProductCode`

My problem is i want to expand it that im using the value of $Despatch to search another column in the same table using an OR function. So the query will look into Despatch Number and if it does not find one matching then it looks into order number as below. It doesnt work and my mysql server just hangs trying to run this query.

      SELECT  `Despatch`.`DespatchNumber` , `Despatch`.`Product` ,
           `Stock`.`ProductNumber` ,  `Stock`.`Description` ,  `Stock`.`ProductGroup` ,  `Stock`.`Size` ,  `Stock`.`IPICODE`  , `Stock`.`Fit` , `Stock`.`62` 
            FROM Stock , Despatch 
            WHERE  `Despatch`.`DespatchNumber` = '" . $Despatch . "'   
            OR  `Despatch`.`OrderNumber` = '" . $Despatch . "' 
            AND   `Despatch`.`Product`  =    `Stock`.`ProductCode`

Mark Byers · Accepted Answer · 2012-10-24 11:31:07Z

1

You need parentheses around your OR clause:

WHERE (`Despatch`.`DespatchNumber` = '" . $Despatch . "'   
OR     `Despatch`.`OrderNumber` = '" . $Despatch . "') 
AND   `Despatch`.`Product`  =    `Stock`.`ProductCode`

I'd also very strongly recommend you to explicitly use the JOIN keyword when you perform joins. Not only is this best practice, but it also would have prevented the error occuring in this case.

SELECT -- ...columns...
FROM Stock AS S
JOIN Despatch AS D
ON D.Product = S.ProductCode
WHERE D.DespatchNumber = 'DN001234'
OR D.OrderNumber = 'DN001234'

edited Oct 24, 2012 at 11:31

answered Oct 24, 2012 at 11:26

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query multiple fields in sql query using same variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related