What is a good way to format this nested sql statment?  Are there any
good sites showing other examples?  Thanks !

SELECTMerchandise.Description, Users.Name FROMUsers INNER JOIN (Sales
INNER JOIN (Merchandise INNER JOIN Types ON Merchandise.TypeID =
Types.TypeID) ON Sales.MercID = Merch andise.MercID) ON Users.UserID =
Sales.BuyerID WHERE (((Types.TypeID) = 1));

Here's how I would do it, without the nesting (looks like this query was
born in Access?)... of course formatting is a very subjective subject and
everyone has his/her own tastes.  I know this isn't how Dr. Tom would format
it:

SELECT
    m.Description,
    u.Name
FROM
    Users u
INNER JOIN
    Sales s
    ON u.UserID = s.BuyerID
INNER JOIN
    Merchandise m
    ON s.MercID = m.MercID
INNER JOIN
    Types t
    ON m.TypeID = t.TypeID
WHERE
    t.TypeID = 1;




<bringmewa***@gmail.com> wrote in message
Show quoteHide quote

My prefered formatting would be:


SELECT
    Merchandise.Description,
    Users.Name
FROM
    Users
INNER JOIN Sales
    ON Users.UserID = Sales.BuyerID
INNER JOIN Merchandise
    ON Sales.MercID = Merchandise.MercID
INNER JOIN Types
    ON Merchandise.TypeID = Types.TypeID
WHERE (((Types.TypeID) = 1));

I think that a religious question, everone has a special way to write
his query, the main target should be to make the query more readable so
that also not deeply involoved people could have an idea of your query
when they are looking on it.


HTH, jens Suessmeyer.

I'd do something like

SELECT
    MER.Description,
    USER.Name
FROM Users USR
INNER JOIN Sales SLS
ON USR.UserId = SLS.BuyerID
INNER JOIN Merchandise  MER
ON MER.MercId = SLS.MercId
INNER JOIN Types  TYP
ON MER.TypeID = Types.TypeID
AND TYP.TypeId = 1


I think onething everybody will agree is starting each keyword in a new
line.

Show quoteHide quote

How about including some indents like this?  Is this easier to
understand ?

SELECT Merchandise.Description, Users.Name
FROM Users
JOIN
(
    Sales JOIN
    (
        Merchandise JOIN Types ON Merchandise.TypeID = Types.TypeID
    ) ON Sales.MercID = Merchandise.MercID
) ON Users.UserID = Sales.BuyerID
WHERE ((Types.TypeID) = 1);

No, it looks confusing to me. I only use paranthesis that way when joining a
sub-query.

Also, there is no need to enclose the where clause with paranthesis unless
you have compound conditions.

For example:

where
    (
        (x = 1 or x = 2) and
        (y = 0)
    )
    or
    (y = 1)

<bringmewa***@gmail.com> wrote in message
Show quoteHide quote

<bringmewa***@gmail.com> wrote in message
Show quoteHide quoteThe parentheses are bad.  You should only include parens when you need to
force (or indicate) the order of operations.

Here you have two inner join operations and you are specifying the order of
operations:

    Users JOIN (Sales JOIN Merchandise ON ...) ON ...

But JOIN (or INNER JOIN) is an associative operation.

    Users JOIN (Sales JOIN Merchandise ON ...) ON ...

Is equivilent to

    (Users JOIN Sales ON ...) JOIN Merchandise ON ...

Which is equivilent to

    Users JOIN Sales ON ... JOIN Merchandise ON ...

So it is better formatting to omit the parentheses and keep each ON clause
next to its related JOIN.

INNER JOIN is associative, but LEFT JOIN is not.  And RIGHT JOIN can usually
be rewritten as LEFT JOIN.  Moreover

   (A LEFT JOIN B) LEFT JOIN C

Is equivilent to

  (A LEFT JOIN C) LEFT JOIN B

Which is equivilent to
  A LEFT JOIN B LEFT JOIN C

So if you list all of your INNER JOIN's first, followed by all of your LEFT
JOINS then you can dispense with parentheses altogether. Your joins will be
logically processed in left-to-right (or top-to-bottom) order, which makes
analysis of the query easier.

SELECT Merchandise.Description, Users.Name
FROM Users
JOIN Sales
   ON Users.UserID = Sales.BuyerID
JOIN Merchandise
   ON Sales.MercID = Merchandise.MercID
JOIN Types
   ON Merchandise.TypeID = Types.TypeID
WHERE Types.TypeID = 1;


Also in the WHERE clause parens should be avoided except where they indicate
the order of operations between different boolean operators.  The rule here
is that you should never expect someone to remember the order of operations
between AND and OR.  If you mix AND and OR, use parentheses and spacial
formatting to indicate the order of operations.  Also by convention use only
AND at the top level of your WHERE clause.


EG:

  WHERE A = 1
    AND (B = 2 OR A=3)

David

How about these two?

SELECT Merchandise.Description, Users.Name
  FROM Users
       JOIN (Sales
             JOIN (Merchandise
                   JOIN Types
                     ON Merchandise.TypeID = Types.TypeID)
               ON Sales.MercID = Merchandise.MercID)
         ON Users.UserID = Sales.BuyerID
WHERE Types.TypeID = 1;

or, taking advantage of the fact that joins can be commuted:

SELECT Merchandise.Description, Users.Name
  FROM Merchandise
       JOIN Types ON Merchandise.TypeID = Types.TypeID
       JOIN Sales ON Sales.MercID = Merchandise.MercID
       JOIN Users ON Users.UserID = Sales.BuyerID
WHERE Types.TypeID = 1;

<bringmewa***@gmail.com> wrote in message
Show quoteHide quoteYep, that's it.

David

David Browne (davidbaxterbrowne no potted m***@hotmail.com) writes:
I used to think so during my many years of Pascal programming, but these
days when I'm doing mainly SQL, I've stopped doing that. The less
parens, the less line noise. After all, it's simple: AND is multiplication
and OR is addition. And every one knows the priority of + and *, don't
they?

Also, SQL is quite different from Pascal and similar langauges. AND is
in about each and every query. OR is not at all equally common.

Hmm.  Lots of people use SQL without a background in any programming
language or math.

It's exactly the rarity of OR that erodes the collective knoledge about
order of operations among SQL folk, and reduces the benefit of expressing
OR's with shorter syntax.

The clincher for me is that when I read SQL code written by someone else,
I'm not certian that they understood the order of operations.

David

Your original query had what seems to be unneeded parenthesis in odd places,
but I think that my version below is functionally equivalent. I've found
this convention to be useful for very long and complex queries that require
frequent maintenance and revisions.

1.    Break each selected column on a new line.
2.    Break each joined table on a new line.
3.    Break each on.. and where.. condition on a new line.
4.    Group together and indent the joined tables in such a way that their
hierarchal relationship to each other is obvious.

select
Merchandise.Description,
Users.Name
from Users
inner join Sales
  on Users.UserID = Sales.BuyerID
  inner join Merchandise
   on Sales.MercID = Merchandise.MercID
   inner join Types
    on Merchandise.TypeID = Types.TypeID
where
Types.TypeID = 1


<bringmewa***@gmail.com> wrote in message
Show quoteHide quote

As others have said, formatting is a matter of personal preference.  I
like to read right to left and I like line breaks on major keywords, so
I use

SELECT m.Description,  u.Name
FROM Users u
INNER JOIN Sales s ON u.UserID = s.BuyerID
INNER JOIN Merchandise m ON s.MercID = m.MercID
INNER JOIN Types t ON m.TypeID = t.TypeID
WHERE  t.TypeID = 1;

For me, SQL is easier to grasp as a single entity when it is a little
more condensed vertically.

I don't break on "ON" because I consider it a part of the "JOIN"
clause.  I do like capitalized keywords and short, lowercase table
aliases.  I don't like unnecessary parenthesis.

Payson



bringmewa***@gmail.com wrote:
Show quoteHide quote

I hope you don't mind me piggybagging on your post, Payson :-)

I'm with you, except for one thing. JOIN belong to the FROM clause (technically, according to the
language specs). So I line break on each clause, and since FROM clause can be lengthy, I indent a
bit. Same with SELECT list, if many columns.

SELECT m.Description,  u.Name
FROM Users u
  INNER JOIN Sales s ON u.UserID = s.BuyerID
  INNER JOIN Merchandise m ON s.MercID = m.MercID
  INNER JOIN Types t ON m.TypeID = t.TypeID
WHERE  t.TypeID = 1;

SELECT
m.Description
,u.Name
,g.whatever
FROM...
Show quoteHide quote

Not a bit :)

Never thought of it that way.  I think of JOIN as parallel to FROM as
they both identify source tables, so I don't indent it.  I do indent
continuation lines and I try to stay less than 2/3 page in width...

SELECT  m.Description, u.Name, g.whatever, m.Stuff, m.MoreStuff,
   u.AllThatStuff, g.WhateverElse
FROM ...

Payson

No, not really.  There are measurable, testable things you do to make
text readable.  Your eyes and mind are trained by your native language
and the laws of human visual mechanisms. 

Get a copy of SQL PROGRAMMING STYLE for some of the research results (in
English, not stats).

--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications. 


*** Sent via Developersdex http://www.developersdex.com ***

(bringmewa***@gmail.com) writes:
This form of nested join is a headache to read. You sometimes need this
for outer joins, but here it's better to take the JOIN one by one. I always
leave out INNER and OUTER (unless I use a join hint), to reduce the amount
of noise in the query.

Also, I think use of alias is very important. When you have a complex
query, repeating the table name, is a very good way to hide the
forest with a lot of trees.

Thus, I would write the sample query as:

  SELECT M.Description, U.Name
  FROM   Users U
  JOIN   Sales S  ON U.UserID = S.BuyerID
  JOIN   Merchandise M ON S.MercID = M.MercID
  JOIN   Types T ON M.TypeID = T.TypeID
  WHERE  T.TypeID = 1;

Wait, that seems funny. Don't need Types:

  SELECT M.Description, U.Name
  FROM   Users U
  JOIN   Sales S  ON U.UserID = S.BuyerID
  JOIN   Merchandise M ON S.MercID = M.MercID
  WHERE  M.TypeID = 1;

Get a copy of SQLPROGRAMMING STYLE.  It gives the style I have been
using for awhile and it has been adapted by various magazine and book
publishers.

You also need to learn about data element names, so you will create
meaningless names like "type_id" (impossible -- an attribute is either
a type, with many values or an identifier for one and only one entity.
If yuou want to make it more absurd, try adding more "adjectives"
without a noun so get  "type_id-value_code" ) and "Types" (too vague;
what kind?  merchndise? customers? animals? )

Use of NULL and DEFAULT
Record locking within a stored procedure
compare '002' and '00²'
Evaluation of conditions
complex and large triggers