I have four tables like this
PatientID PatientName PA001 | Danny andrean PA002 | John Travolta PA003 | Danny Lee
TransactionID PatientID TransactionDate TR001 | PA001 | 2012/12/6 TR002 | PA002 | 2013/11/4 TR003 | PA003 | 2010/4/12
TransactionID MedicineID Quantity TR001 | ME001 | 5 TR002 | ME001 | 6 TR003 | ME002 | 5
MedicineID MedicineName MedicineStock ME001 |HIVGOD |100 ME002 |CancerCure |50
How can I show show
TotalMedicineBought (obtained from the amount of Medicine Quantity purchased) where
MedicineID of purchased medicine was 'ME001' and
PatientName consists of 2 words or more.
PatientID | PatientName | Total Medicine Bought PA001 | Danny Andrean | 5 PA002 | John Travolta | 6
I tried this query:
select mp.PatientID,mp.PatientName,SUM(td.Quantity) as TotalMedicineBought from MsPatient mp, TransactionDetail td inner join TransactionHeader th on th.TransactionID = td.TransactionID Group by td.TransactionID, mp.PatientID, mp.PatientName
I don't know how to make a condition that consist two words
I use SQL Server 2008
The problem with the original query is the mixing of different ways of doing joins. You should avoid commas in the
from clause. If you really, really want a
cross join, then use the
cross join statement.
Your problem is one of filtering for the right name and joining the tables together:
select p.PatientID, p.PatientName, sum(case when MedicineId = 'ME001' then Quantity else 0 end) as Total_Medicine_Bought from MsPatient p join TransactionHeader th on p.PatientID= th.PatientID join TransactionDetail td on td.TransactionID =th.TransactionID where PatientName like '% %' group by p.PatientID, p.PatientName;
This query also shows a good use of aliases. For readability, these should be abbreviations of the table names, rather than arbitrary letters. It also shows the use of conditional aggregation. You should easily be able to see how to modify the query to also show
ME002, for instance. Having at least two parts in the patient name seems equivalent to having at least one space.
like is a simple mechanism to look for a single space.