JOIN STATEMENT

akibaMaila · February 8th, 2005, 01:16 PM

Can someone tell me what is wrong with my sql statement? My program works if I do not use the JOIN statement. Help............please.

myDataAdapter.SelectCommand.CommandText = & _
"SELECT ReceivedDrug, DispensedDrug " & _
"FROM Received " & _
"JOIN Dispensed ON Received.ReceivedDrug = Dispensed.DispensedDrug "

gbianchi · February 9th, 2005, 10:02 AM

hi there...

what's your database??? maybe it want you to writte INNER JOIN???

HTH

Gonzalo

BrianWren · February 9th, 2005, 12:48 PM

Try:

Code:

    myDataAdapter.SelectCommand.CommandText = & _
        "SELECT ReceivedDrug, DispensedDrug " & _
        "FROM   Received, Dispensed         " & _
        "WHERE  Received.ReceivedDrug = Dispensed.DispensedDrug"

akibaMaila · February 9th, 2005, 05:34 PM

Thank you both. I finally got it to work.

BrianWren · February 10th, 2005, 12:10 PM

akibaMaila · February 10th, 2005, 04:18 PM

myDataAdapter.SelectCommand.CommandText = "SELECT ReceivedDrug, Received.ScannedDate " & _
"FROM Dispensed RIGHT JOIN (Received LEFT JOIN Destroyed ON Received.ReceivedDrug = Destroyed.Destroyed) ON Dispensed.DispensedDrug = Received.ReceivedDrug " & _
"WHERE ((Dispensed.DispensedDrug) Is Null) AND ((Destroyed.Destroyed) Is Null) "

Brian - I think you helped me out a couple of times. Thanks so much.