Re: Second opinion for a query

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 09/19/04 

Date: Sun, 19 Sep 2004 23:50:20 +0200

On Sun, 19 Sep 2004 02:08:56 -0400, Steve Lewis - Website Nation wrote:

>I wanted to get a second opinion on this:

Hi Steve,

An alternative for your query would be:

SELECT dbo.tblMembers.MemberID, dbo.tblMembers.FirstName,
                dbo.tblImage.Image, dbo.tblImage.ImageOriginalFilename,
                dbo.tblImage.Approved, dbo.tblImage.ImageDisplay
FROM dbo.tblMembers
LEFT OUTER JOIN dbo.tblImage
           ON dbo.tblMembers.MemberID = dbo.tblImage.MemberID
           AND dbo.tblImage.ImageDisplay = 'Thumbnail'
           AND dbo.tblImage.Approved = 'Yes'

>BUT, it there a better way to do this?

You should judge by yourself if you think it's better. Run both against
your database and compare execution plans. Check if you understand the
logic and if you think you'll still understand it when you'll have to get
back to this query in a year or so from now.

>I read that Outer Joins are not
>all that good and that using NULL is not to great either. I looked into
>subqueries, but that seemed inappropriate.

Where did you read about outer joins being bad? I've never heard of this
before.

There are a lot of people claiming that NULLs should be avoided, but I've
yet to see a practical solution for many problems that are now easily
solved using NULLS. And the main argument against NULLS is that some
programmers don't understand how to handle them and mess things up as a
result - true, of course, but doesn't that call for better education
instead of moving to abolish NULLS?

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Left Outer Join: Index Seek not providing all index columns
    ... We have a left outer join query which is intended to prove ... The wrinkle is that the SQL Server 2000 query optimizer is generally ... left outer join entextractitems eei ...
    (microsoft.public.sqlserver.server)
  • Re: Cond. Formatting w/ DLookup
    ... I'd like to be able to indicate on the first form, ... form's record source to be a query with an outer join of the ... This form is continuous (actually a subform), ... Using your excellent idea of the outer join, ...
    (microsoft.public.access.formscoding)
  • Re: Optimising the Query
    ... SUM ... FROM TABLE3 ... The query is taking records from TABLE2 and TABLE3 and LEFT OUTER JOIN ...
    (comp.databases.oracle.misc)
  • Re: Cond. Formatting w/ DLookup
    ... I'd like to be able to indicate on the first form, ... IvId field, then a better way would be to change the first ... form's record source to be a query with an outer join of the ... Using your excellent idea of the outer join, ...
    (microsoft.public.access.formscoding)
  • Re: Figuring out what field is causing key violation on append
    ... Nz converts nulls to whatever you put as the second argument. ... Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia ... First try the query with just one field in the append query. ...
    (microsoft.public.access.queries)