Re: Subselect Query Problem

Hi,


A left join, say

 tableA LEFT JOIN tableB


is like an inner join, except that if one record of tableA would not be in 
the result (of the inner join), it is logically re-introduced into the 
result, but since nothing match in tableB, whatever comes from tableB is 
filled with NULL, in that row.

tableA
f1
1
2
3

tableB
g1
1
3


SELECT f1, g1 FROM tableA LEFT JOIN tableB ON tableA.f1=tableB.g1

will be:

f1        g1
1        1
2        null
3        3


note the row f1=2. An inner join would have not include it, in the result. 
The LEFT join reintroduces it, but since there is no g1 that corresponds to 
it, a value still has to be put under this column, a null is supplied.

The WHERE clause checks for the presence of this NULL to determine that f1 
was without match in g1. Indeed, the WHERE clause examine the result of the 
JOIN, not the initial rows of the tables!


Hoping it may help,
Vanderghast, Access MVP



"robert d via AccessMonster.com" <u6836@uwe> wrote in message 
news:5b40a513482c6@xxxxxx
> Michael:
>
> Thank you for your response.  I'm testing what you posted in the Query 
> area
> of Access (although I'm intending to implement this as SQL in VBA code 
> using
> DAO).
>
> It looks like it works, but I made a mistake in my original post.  Namely, 
> it
> is Table B that has the first three fields as the primary key, not Table 
> A.
> Table A actually uses an autonumber primary key.
>
> I'm not sure if this would change your construct or not.
>
> Also, I got to thinking about the Sub_Proposal_Status.  Since this value 
> is
> set in code from forms where the user makes a selection or not, there is 
> no
> reason why there shouldn't be a value.  The only reason there currently 
> isn't
> for some rows is  because this code was implemented after there were 
> already
> records in the table and before Sub_Proposal_Status was added as a field 
> to
> the table.   Would you agree it makes good programming sense for this 
> field
> to be set to either "Y" or "N" for every record in Table B.
>
> Also, I've always shied away from Left Joins (when possible).  I've never
> understood why there is the
>
> "WHERE b.project IS NULL" with these Left Joins.
>
> There are no records in Table B where Project Is Null because Project is 
> part
> of the key.  So, I don't understand what this phrase does.  Isn't it like
> saying "WHERE b.project <> "Molly".  Well, for the example rows I've 
> posted,
> this is always true, so wouldn't just this criteria return all rows!!??
>
> Thanks for any insight you can provide.
>
> Michel Walsh wrote:
>>Hi,
>>
>>SELECT tableA.*
>>FROM tableA LEFT JOIN (SELECT project, status FROM tableB WHERE
>>SubProjectInd ='Y') As b
>>    ON tableA.project = b.project AND tableA.status=b.status
>>WHERE b.project IS NULL
>>
>>return records in tableA not in the set of records in tableB where
>>SubProjectInd='Y' (we only test the project and status value, not the date
>>value, but we can add it if it is required, in the ON clause).
>>
>>Save that query as q1.
>>
>>SELECT d.project, LAST(d.status), d.date
>>FROM q1 AS d INNER JOIN q1 As c ON c.project=d.project
>>GROUP BY d.project, d.date
>>HAVING d.date=MAX(c.date)
>>
>>should return one record associated to the latest date (per project), 
>>given
>>what is kept in q1.
>>
>>Hoping it may help,
>>Vanderghast, Access MVP
>>
>>> I'm having trouble constructing a query to retrieve records from a 
>>> TableA
>>> based on an indicator in TableB and on getting the max date.  Here's the
>>[quoted text clipped - 32 lines]
>>> of either 'Y" or "N" (but with no Nulls or spaces), then this can be
>>> arranged.
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200602/1 


.

Relevant Pages

  • Re: INNER JOIN newbie question
    ... > I am building queries joining data from many tables ... > relationship with TableB ... > SELECT * FROM TableA INNER JOIN ...
    (microsoft.public.sqlserver.programming)
  • Re: How to trim a record when using DB_OPEN_TABLE
    ... and TableB contains some items that need to be updated in TableA. ... EmplID ... QtrDate (primary key) ...
    (microsoft.public.access.modulesdaovba)
  • Re: sql minus
    ... What do you do in the case of a composite primary key? ... FROM TableA LEFT JOIN TableB ... SELECT * FROM TableA MINUS SELECT * FROM TableB; ...
    (microsoft.public.access.queries)
  • Re: Cursors (again)
    ... What does it matter if TableB is temporary? ... or tableA. ... open cTableB ...
    (microsoft.public.sqlserver.programming)
  • Re: Eliminating a sub-select from a query
    ... INNER JOIN ... LEFT JOIN tableC ... > tableA a, ... > tableB b ...
    (microsoft.public.sqlserver.programming)