CODEWITHSUNDEEP
mysqlsql
Phil
Phil

Reputation: 1453

mySQL- making use of the OR statement

Ok, so i am having trouble again.

the part of my ERD im having problems querying is my superclass, the superclass goes as follows:

Supply(super) non-surg(sub) surgical(sub) drug(sub)

The primary key is 'OrderNumber'

A staff member who works on a ward puts in a requisition for an item, I want to view all items that have be requested, along with the dates recieved and ordered of all non-pharmecutical items (from the sub classes non-surgical and surgical)

So far I have this:

SELECT requisition.ReqNumber, staff.staffnumber, ward.wardname, itemname
FROM requisition, staff, supply, ward, ward_staff, req_supply, nonsurgical, surgical
WHERE requisition.staffnumber = staff.staffnumber
AND requisition.reqnumber = req_supply.reqnumber
AND supply.ordernumber = req_supply.ordernumber
AND staff.staffnumber = ward_staff.staffnumber
AND ward_staff.wardnumber = ward.wardnumber
AND supply.ordernumber = nonsurgical.ordernumber
OR supply.ordernumber = surgical.ordernumber;

So basically if the primary key of the supercalss is the same as that in the surgical... OR the same as that in the nonsurgical, show me that requisition! the problem lies within the subcalss but I am lost

Thank you for any help you can offer

Upvotes: 0

Views: 112

Answers (4)

jmoreno
jmoreno

Reputation: 13571

Well your main problem is that you're using a difficult to keep straight join syntax. Because of that you are effectively using a left join.

 SELECT r.ReqNumber,
     St.staffnumber,
     w.wardname, itemname
 FROM requisition r
      Inner join   staff st
        On r.staffnumber = st.staffnumber
      Inner join req_supply rs
        On r.reqnumber = rs.reqnumber
      Inner join supply su
        On su.ordernumber = rs.ordernumber
      Inner join ward_staff wst
        On wst.staffnumber = st.staffnumber
      Inner join ward w
        On w.wardnumber = wst.wardnumber
      Inner join nonsurgical  nsr
        On su.ordernumber = nsr.ordernumber
      Left join surgical sr
        On su.ordernumber = sr.ordernumber ;

Upvotes: 1

CLo
CLo

Reputation: 3730

I had a similar issues with several AND clauses and a final OR clause, try surrounding the two ORed clauses in parenthesis.

SELECT requisition.ReqNumber, staff.staffnumber, ward.wardname, itemname
FROM requisition, staff, supply, ward, ward_staff, req_supply, nonsurgical, surgical
WHERE requisition.staffnumber = staff.staffnumber
AND requisition.reqnumber = req_supply.reqnumber
AND supply.ordernumber = req_supply.ordernumber
AND staff.staffnumber = ward_staff.staffnumber
AND ward_staff.wardnumber = ward.wardnumber
AND 
(supply.ordernumber = nonsurgical.ordernumber
    OR supply.ordernumber = surgical.ordernumber);

Upvotes: 1

Maarten
Maarten

Reputation: 106

The OR operator has a lower precedence than the AND operator, so you need to use parenthesis for the last two clauses, otherwise it will be enough to satisfy only the last OR clause.

So:

SELECT requisition.ReqNumber, staff.staffnumber, ward.wardname, itemname
FROM requisition, staff, supply, ward, ward_staff, req_supply, nonsurgical, surgical
WHERE requisition.staffnumber = staff.staffnumber
AND requisition.reqnumber = req_supply.reqnumber
AND supply.ordernumber = req_supply.ordernumber
AND staff.staffnumber = ward_staff.staffnumber
AND ward_staff.wardnumber = ward.wardnumber
AND (supply.ordernumber = nonsurgical.ordernumber
OR supply.ordernumber = surgical.ordernumber);

Upvotes: 3

Joe Stefanelli
Joe Stefanelli

Reputation: 135928

Put parentheses around the OR condition to get the order of precedence right.

...
AND (supply.ordernumber = nonsurgical.ordernumber
  OR supply.ordernumber = surgical.ordernumber);

Upvotes: 3

Related Questions