CODEWITHSUNDEEP
mysqlstored-procedures
Nikita Silverstruk
Nikita Silverstruk

Reputation: 1117

MySQL stored procedure: IF in WHERE

I am trying to select some data in the following manner:

SELECT column
FROM table
WHERE a = a1
AND (b = b1 OR b = b2 OR b = b3);

What I want it to do is if b is not equal to b1, check if b=b2. However, if b=b1, do not check other conditions. The result of this select statement must be only one entry. However, in the statement I have no, it checks all of the three conditions and sometimes returns multiple rows. Again, I would like it to stop checking if the condition is true.

Any ideas on how could this be implemented? I tried case but it did not work out...

Thank you in advance!

EDIT Here is an actual query i am trying to run.

INSERT INTO shipment_flights 
    (airlinename, flt_no, flt_date, destination, phone, depttime, arrivaltime, pcs, weight)
SELECT st.airlinename, flightno, flightdate, destination,     
(SELECT phone 
FROM carrierlocations 
WHERE carriers_carrierid = (select carrierid from carriers where airlinename = st.airlinename) 
AND (city = destination OR (city != destination AND
    city = (SELECT city FROM airports WHERE iataid = 
            (SELECT airports_iataid FROM ratelegs 
                WHERE shipments_shipid = c.shipments_shipid))
                ))) phone, 
depttime, arrivaltime, sum(linepcs), sum(lineweight)
FROM segment_times st
    JOIN contents2flights c2f 
        ON st.flightid = c2f.segments_flights_flightid 
        AND st.segmentid = c2f.segments_segmentid 
    JOIN contents c 
        ON c.lineno = c2f.contents_lineno 
        AND c.shipments_shipid = c2f.contents_shipments_shipid

WHERE c.shipments_shipid = var_shipid
GROUP BY flightid
ORDER BY flightdate, depttime;

Here is a sample output:

airlinename         flt_no  flt_date        destination    phone         pcs   weight
Everts Air Alaska   CH1     2008-02-20      Hughes         9074502351    24    2121

The query inserts bunch of flight data into temporary table. What I am having trouble with is getting a phone number for a location. This part is as follows:

(SELECT phone 
FROM carrierlocations 
WHERE carriers_carrierid = (select carrierid from carriers where airlinename = st.airlinename) 
AND (city = destination OR (city != destination AND
    city = (SELECT city FROM airports WHERE iataid = 
            (SELECT airports_iataid FROM ratelegs 
                WHERE shipments_shipid = c.shipments_shipid))))) phone

In the query advised by Amit Bhargava, I get the right result only if there is one row in the temporary table. If there are more, it throws an error in the selecting phone part.

"Error Code: 1242. Subquery returns more than 1 row"

Upvotes: 3

Views: 1381

Answers (2)

DRapp
DRapp

Reputation: 48169

By using IF() + IF() + IF(), and testing the sum = 1 prevents a lot of extra and not of the other criteria. If 2 or all 3 are the same, the summation will be greater than 1. If none of them match, the result is 0. This should get exactly what you want. 

SELECT column
  FROM table
  WHERE a = a1
    AND  if( b = b1, 1, 0 )
       + if( b = b2, 1, 0 )
       + if( b = b3, 1, 0 ) = 1

Or... as assisted by ypercube, and I keep forgetting logical tests return either 1 or 0 for true / false respectively, such as...

SELECT column
  FROM table
  WHERE a = a1
    AND  (b = b1) + (b = b2) + (b = b3) = 1

Upvotes: 3

Chetter Hummin
Chetter Hummin

Reputation: 6817

Please try the following. Not the most elegant solution, but it should work.

SELECT column
FROM table
WHERE a = a1
AND (b = b1 OR (b != b1 AND (b = b2 OR (b != b2 AND b = b3))))

Upvotes: 1

Related Questions