I have four tables like this

Table 1: MsPatient

PatientID PatientName
PA001    | Danny andrean
PA002    | John Travolta
PA003    | Danny Lee

Table 2: TransactionHeader

TransactionID PatientID TransactionDate
TR001        | PA001   | 2012/12/6
TR002        | PA002   | 2013/11/4
TR003        | PA003   | 2010/4/12

Table 3: TransactionDetail

TransactionID MedicineID Quantity
TR001        |   ME001  | 5
TR002        |   ME001  | 6
TR003        |   ME002  | 5

Table 4: MsMedicine

MedicineID MedicineName MedicineStock
ME001     |HIVGOD      |100
ME002     |CancerCure  |50

How can I show show PatientID, PatientName, and TotalMedicineBought (obtained from the amount of Medicine Quantity purchased) where MedicineID of purchased medicine was 'ME001' and PatientName consists of 2 words or more.

Example:

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.

more