Using Summary Data as a Parameter in SQL

Question

I'm new to SQL and am using Access to run queries that Excel can't really handle. Here's the basic design of the query:

SELECT A.ID, A.Description, A.Location, B.ID, B.Quantity, B.Location
FROM A LEFT JOIN B ON A.ID = B.ID

In table B, location is all the same value. I want to retain the left join above, but limit the resulting values in table A to whatever the location value is in column B. In my mind this would be a WHERE clause in which A.Location = max(B.Location) or something like that.

Any ideas?

hashbrown · Accepted Answer · 2014-11-06 18:38:05Z

2

If you want to limit the resulting values in table A to whatever the location value is in table B, why can't you simply use the join based on location also?

SELECT A.ID, A.Description, A.Location, B.ID, B.Quantity, B.Location
FROM A LEFT JOIN B 
ON A.ID = B.ID
AND A.location = B.location

answered Nov 6, 2014 at 18:38

hashbrown

3,5461 gold badge23 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Buckminster Nasty Over a year ago

Because table A has all kinds of location data that I don't want. I want to limit what is returned from table A based on whichever location is present in Table B. Does that make sense?

Buckminster Nasty Over a year ago

Sorry @hashbrown that was unclear. Here's try number two: Because I want to retain rows where A.ID = B.ID but B.location is a null. Basically, B.Location only has one value, and I want to run a WHERE clause that limits the values in A.location to be whatever that value is.

HansUp · Accepted Answer · 2014-11-06 19:26:21Z

0

You can use a DMax expression to fetch the duplicated non-Null value of B.Location. And that expression can be used in the WHERE clause to limit A rows to only those with matching [Location]:

SELECT A.ID, A.Description, A.Location, B.ID, B.Quantity, B.Location
FROM A LEFT JOIN B ON A.ID = B.ID
WHERE A.Location = DMax("[Location]", "B");

If you prefer not to use DMax since it is Access-specific, you can do it this way instead:

SELECT A.ID, A.Description, A.Location, B.ID, B.Quantity, B.Location
FROM A LEFT JOIN B ON A.ID = B.ID
WHERE A.Location = (SELECT Max([Location]) FROM B);

edited Nov 6, 2014 at 19:26

answered Nov 6, 2014 at 19:18

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

1 Comment

Buckminster Nasty Over a year ago

That did it! Thanks, I've never come across a DMax or DMin function. Super helpful!

Collectives™ on Stack Overflow

Using Summary Data as a Parameter in SQL

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related