Re: SQL question ?

From: Murali.L (muralil_at_olamtanzania.com)
Date: 07/02/04 

Date: Fri, 2 Jul 2004 10:15:48 +0300

Thanks Jeff really good examples...

Murali.

"Jeff Johnson [MVP: VB]" <i.get@enough.spam> wrote in message
news:OJa4P83XEHA.3536@TK2MSFTNGP11.phx.gbl...
>
> "Murali.L" <muralil@olamtanzania.com> wrote in message
> news:O1eUPa3XEHA.2844@TK2MSFTNGP12.phx.gbl...
>
> > I wrote SQL with join but it gives me an error... so I removed the SQL,
I
> > dont have now. But i wanted to know how to writer the
> > SQL with Join. If u can give me the syntax, that will help me.
>
> SELECT <columns>
> FROM <table1> <alias1> INNER JOIN <table2> <alias2>
> ON <alias1>.<column> = <alias2>.<column>
> [INNER JOIN <table3> <alias3> ON <alias3>.<column> = <alias1 or
> 2>.<column>]
> [...and so on]
>
> Example (2 tables):
>
> SELECT c.CustomerName, o.OrderNumber, o.OrderDate
> FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID
>
> Example (3 tables):
>
> SELECT c.CustomerName, o.OrderNumber, o.OrderDate, s.ShipperName
> FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID
> INNER JOIN Shippers s ON o.ShipperID = s.ShipperID
>
> Example (4 tables--notice that one table serves only to link two others):
>
> SELECT c.CustomerName, o.OrderNumber, o.OrderDate, p.PartName
> FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID
> INNER JOIN OrderItems oi ON oi.OrderID = o.OrderID
> INNER JOIN Parts p ON p.PartID = oi.PartID
>
>
> Aliases are not required but I personally think writing out table names in
> front of each column makes the SQL way too wordy. Others will disagree.
>
> Also, the order of the columns in the ON clause is not significant when
> using = (which you will use 99.999999999999999999999999% of the time). You
> can use
>
> ON c.CustomerID = o.CustomerID
>
> or
>
> ON o.CustomerID = c.CustomerID
>
>

Relevant Pages

  • Re: Should be simple but....
    ... FROM Customers C ... INNER JOIN ORDERS O ... INNER JOIN [ORDER DETAILS] OD ...
    (microsoft.public.sqlserver.programming)
  • Re: Counting rows by a dynamic SQL statements
    ... SELECT @cnt = COUNT ... INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID ... INNER JOIN ON Orders.OrderID =.OrderID ... SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Question
    ... SQL solution given, but when running the program, i get the following error: ... INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID ...
    (microsoft.public.vb.general.discussion)
  • Re: SQL Question
    ... SQL solution given, but when running the program, i get the following error: ... INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID ... INNER JOIN Product ON OrderItem.ProductID = Product.ProductID ...
    (microsoft.public.vb.general.discussion)
  • Re: SQL to Linq - Left, Right and Inner Joins
    ... I'd certainly hope that the query optimiser would do the job there though. ... if the where clause is on an indexed column I'd be *hugely* surprised if the inner join were fully evaluated by SQL server before looking at the index. ... select distinct c.* from customers c inner join orders o ...
    (microsoft.public.dotnet.languages.csharp)