Filtering one record per master in a one to many join

Filtering one record per master in a one to many join

Post by Greg Brune » Sat, 27 Jan 2001 12:45:32



I'm having some problems with Temp Tables (see that message), but as long as
I'm asking for ideas, perhaps there is a way to do this in SQL that I may be
overlooking.  I am trying to perform a join between a master table and a
related table, where there may be multiple matching records in the related
table.  What I want is the "best available" record from the related table
for the join.  For example the master has: Keyfield, Other Data.  The
related table has Keyfield, DataLevel, & Other Data, where DataLevel might
be an integer and the lower the value the more preferred the record.  My
select looks something like:

SELECT m.Keyfield, r.DataLevel, m.aa, r.bb, r.cc FROM master AS m INNER JOIN
related AS r ORDER BY m.Keyfield, r.DataLevel

Since there is a one-many relationship between the tables, I'm getting 1 or
more matches per Master record.  I only want the first matching record for
each KeyField however, the one with the lowest DataLevel value.  Is there a
way to code this in SQL such that I can get just that one record without
having to get them all, then step through the recordset with procedural
code?  Also, the queries involved in the query have to be dynamically
created, so I won't be able to use a stored procedure

Thanks very much for any help,

-- Greg
Semper Software, Inc.
www.SemperSoft.com

 
 
 

Filtering one record per master in a one to many join

Post by Matthew Bur » Sat, 27 Jan 2001 15:15:40


SELECT m.Keyfield, r.DataLevel, m.aa, r.bb, r.cc
FROM master AS m INNER JOIN related AS r
ON m.KeyField = r.KeyField
WHERE r.DataLevel = (SELECT MIN(r2.DataLevel)
                                         FROM related AS r2
                                        WHERE r2.KeyField = r.KeyField)
ORDER BY m.KeyField, r.DataLevel

Matthew Burr

> I'm having some problems with Temp Tables (see that message), but as long
as
> I'm asking for ideas, perhaps there is a way to do this in SQL that I may
be
> overlooking.  I am trying to perform a join between a master table and a
> related table, where there may be multiple matching records in the related
> table.  What I want is the "best available" record from the related table
> for the join.  For example the master has: Keyfield, Other Data.  The
> related table has Keyfield, DataLevel, & Other Data, where DataLevel might
> be an integer and the lower the value the more preferred the record.  My
> select looks something like:

> SELECT m.Keyfield, r.DataLevel, m.aa, r.bb, r.cc FROM master AS m INNER
JOIN
> related AS r ORDER BY m.Keyfield, r.DataLevel

> Since there is a one-many relationship between the tables, I'm getting 1
or
> more matches per Master record.  I only want the first matching record for
> each KeyField however, the one with the lowest DataLevel value.  Is there
a
> way to code this in SQL such that I can get just that one record without
> having to get them all, then step through the recordset with procedural
> code?  Also, the queries involved in the query have to be dynamically
> created, so I won't be able to use a stored procedure

> Thanks very much for any help,

> -- Greg
> Semper Software, Inc.
> www.SemperSoft.com



 
 
 

1. Queries that return one and only one master record

I have a system with a client master, and activity that describes work
done keyed by type code, and a tickler file keyed on tickler number.
Tickler and activity are linked to the Client master by client_num in
a query.  The relationships between client and both activity and
tickler are one to many.  I want to pass query arguments on fields in
all three files and have the answer table show one and only one
instance of each client in the table.  This is to be used to generate
letters so I don't want duplicates.  Does anyone have an idea.
Everything I try either drops a valid client or shows multiple records
for a client.

Thanks

2. Lotus Approach 96: Error or wanted?

3. combine many records into one and one record into many

4. simple query help

5. One record to one or many records

6. FoxPro 2.6 and BMP

7. sql2k join question - only want one match per left regardless of multiples on right

8. error 3450

9. Is master-detail a ONE-TO-ONE paradigm???

10. master - detail in one row, details in one extra column

11. synchronisation between two servers (one master and one backup)

12. Insert Multiple .txt files to one table, 1file per record

13. One To Many Join To Return One Row