sql statement conditions

sql statement conditions

I want to select all the Female patients from the patient table where the area = south or area= west and then group the result by Disease name So I had to write the where condition like this :

command10.CommandText = "SELECT D.DiseaseName, COUNT(D.Patient_ID) AS PNO FROM PatientAffectDisease D INNER JOIN patient P on D.Patient_ID = P.Patient_ID WHERE P.Gender='" & "female" & "'" & " AND P.Area='" & "south" & " '" & "OR P.Area='" & "west" & " '" & " GROUP BY DiseaseName "  

But this doesn't return the right result.

Any Idea?

Answer by Don Dickinson for sql statement conditions

Put parenthesis around your OR'd conditions

e.g.

WHERE P.Gender='" & "female" & "'" & " AND (P.Area='" & "south" & " '" & "OR P.Area='" & "west" & " '" & ")

or just use an IN clause ... where p.gender = 'female' and p.area in ('south', 'west')

Answer by competent_tech for sql statement conditions

The issue is that you had extra spaces after south and west with this code: " '"

You were trying to find 'south ' or 'west ', not 'south' or 'west'.

You can also modify this condition to use an IN clause.

command10.CommandText = "SELECT D.DiseaseName, COUNT(1) AS PNO FROM PatientAffectDisease D INNER JOIN patient P on D.Patient_ID = P.Patient_ID WHERE P.Gender='female' AND P.Area IN ('south', 'west') GROUP BY DiseaseName"  

Answer by Ghost for sql statement conditions

I think the problem is in your where clause specifically related to not using parentheses.

command10.CommandText =   "SELECT D.DiseaseName, COUNT(D.Patient_ID) AS PNO " & _  " FROM PatientAffectDisease D " & _  " INNER JOIN patient P on D.Patient_ID = P.Patient_ID " & _  " WHERE P.Gender='female' AND P.Area in ('south','west') " _  " GROUP BY DiseaseName "  

Answer by Filip Rosen - refp for sql statement conditions

The reason your posted query isn't working properly is because you have an extra space after 'west' and 'south' in the generated query.

You should always group your logic with () to make it easier to maintain and understand the code - and keep away from bugs such as this one.

AND binds harder than OR, so what you had earlier was the same as writing:

(P.Gender = 'female' AND P.Area = 'west') OR P.Area = 'south' -- not correct  

---

Instead of using P.Area = 'west' OR P.Area = 'south' you can use the IN operator, as in the below example:

SELECT     D.DiseaseName, COUNT(D.Patient_ID) AS PNO   FROM       PatientAffectDisease D  INNER JOIN patient P ON D.Patient_ID = P.Patient_ID  WHERE      P.Gender = 'female' AND P.Area IN ('west','south')  GROUP   BY D.DiseaseName  

---

command10.CommandText = "SELECT D.DiseaseName, COUNT(D.Patient_ID) AS PNO FROM PatientAffectDisease D INNER JOIN patient P ON D.Patient_ID = P.Patient_ID WHERE P.Gender = 'female' AND P.Area IN ('west','south') GROUP BY D.DiseaseName"  

Answer by Andrew Shepherd for sql statement conditions

Here is the text of your query:

SELECT      D.DiseaseName,      COUNT(D.Patient_ID) AS PNO   FROM PatientAffectDisease D       INNER JOIN patient P on D.Patient_ID = P.Patient_ID   WHERE P.Gender='female'        AND P.Area='south '        OR P.Area='west '  GROUP BY DiseaseName   

In SQL, the AND naturally has precendence over the OR.

So you're effectively asking

 WHERE (P.Gender='female' AND P.Area='south') OR (p.Area = 'west' )  

You must use brackets to explicitly state the precedence you need

 WHERE P.Gender='female' AND (P.Area='south' OR p.Area='west')  

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72