CODEWITHSUNDEEP
c#mysqlasp.netsql
Michael
Michael

Reputation: 85

Retrieving Data From Three Table

                cmd =new SqlCommand("Select i.InvoiceNo,i.TotalAmount,
                     i.PaymentStatus,m.MovieID,m.MovieName, s.CompanyName
                     From InvoiceDetails i INNER JOIN Movie m ON 
                     i.InvoiceNo=m.InvoiceNo and Supplier s 
                     Inner Join Movie m ON s.SupplierID=m.SupplierID 
                     Where SupplierID=@supplierID AND 
                     CompanyName=@companyName AND PaymentStatus=@ddlPaymentStatus",
                     conPayment);

This is the wrong Select Statement and How can I modify it in order successful to get the data from three table.

Upvotes: 0

Views: 67

Answers (1)

Filipe Silva
Filipe Silva

Reputation: 21657

The one thing that jumps out at me is one JOIN done incorrectly:

You have:

...
from InvoiceDetails i
inner join Movie m on i.InvoiceNo = m.InvoiceNo
  and Supplier s
inner join Movie m on s.SupplierID = m.SupplierID
...

It should be:

select i.InvoiceNo,i.TotalAmount,i.PaymentStatus,m.MovieID,m.MovieName,s.CompanyName
from InvoiceDetails i
inner join Movie m on i.InvoiceNo = m.InvoiceNo
inner join Supplier s on s.SupplierID = m.SupplierID
where s.SupplierID = @supplierID
  and s.CompanyName = @companyName
  and i.PaymentStatus = @ddlPaymentStatus

You already joined Movie once, you have to then JOIN on supplier also. The way you are doing it will surely give you a syntax error. Once for having two tables with the same alias, and then it would also complaint about not knowing supplier s, since it would be expecting a column from the tables joined before that.

Upvotes: 1

Related Questions