Help with a Join - join only first or max?

Hopefully someone will have some ideas on how to do this. I'm
officially stumped.

I have two entities to join. Simplified descriptions follow:

The first has names and addresses (vwPersonAddress) keyed by PersonID
(it is actually a view on two tables, but it works exactly as I want
it to, so all good there).

vwPersonAddress
-------
personID (PK)
addrType (PK)
fname
lname
addr1
city
st

entity 2 is a table that lists licenses and companies and is 1 to many
with vwPersonAddress (a person can have multiple licenses).

vwLicCo
---
personID (PK)
licenseID (PK)
licNum
CompanyName (can be null)

Now the odd part, I want to join them - but only get the first or max
company name from entity 2 for a given person. In other words, the
customer doesn't care WHAT company name I put in the output, as long
as it's only 1 (doesn't create extra records) and belongs to that
person. OH - and to keep it interesting, not every person will have a
company name in that second table...

I've played around quite a bit with all the join types and not found a
way to say, "Join these two tables, outer join on table 2 but if there
IS a match, only give me one"... that 'give me only one' bit is why I
was looking at max() by the way.

What I'm doing right now is running my output query on
vwPersonAddress, then as I create a data file (programmatically) doing
another query FOR EACH ROW on vwLicCo and just grabbing the first
companyName, if any. As you might guess, performance is less than
stellar. ;-)

Any thoughts?On Fri, 10 Dec 2004 12:24:16 -0700, csk wrote:

>Hopefully someone will have some ideas on how to do this. I'm
>officially stumped.
>I have two entities to join. Simplified descriptions follow:

Hi csk,

In the future, please post actual CREATE TABLE scripts, including all
constraints. And add in some sample data (as INSERT statements) plus
expected output, to clarify what you mean and to enable easy testing.

See www.aspfaq.com/5006

>The first has names and addresses (vwPersonAddress) keyed by PersonID
>(it is actually a view on two tables, but it works exactly as I want
>it to, so all good there).
>vwPersonAddress
>-------
>personID (PK)
>addrType (PK)
>fname
>lname
>addr1
>city
>st

I'm confused. Is personID the PK (as you state in the narrative) or is
there a compound key of personID + addrType (as suggested in the list
above)? This is where a CREATE TABLE statement with constraints would have
been better!

>entity 2 is a table that lists licenses and companies and is 1 to many
>with vwPersonAddress (a person can have multiple licenses).
>vwLicCo
>---
>personID (PK)
>licenseID (PK)
>licNum
>CompanyName (can be null)

If vwPersonAddress has personID/addrType as compound key, then this table
needs addrType as well to make a 1 to many relationship.

I guess that actually, both vwLicCo vwPersonAddress are related one to
many with a third table, Persons. They are not directly related. (A
licence belongs to a person, not to a person's home address or work
address).

>Now the odd part, I want to join them - but only get the first or max
>company name from entity 2 for a given person. In other words, the
>customer doesn't care WHAT company name I put in the output, as long
>as it's only 1 (doesn't create extra records) and belongs to that
>person. OH - and to keep it interesting, not every person will have a
>company name in that second table...

Do you still want to list the person (with company name NULL) or should
the person be completely omitted? This is where sample data and expected
output would have been better!

>I've played around quite a bit with all the join types and not found a
>way to say, "Join these two tables, outer join on table 2 but if there
>IS a match, only give me one"... that 'give me only one' bit is why I
>was looking at max() by the way.

I'll have to start making assumptions and wild guesses and I can't run any
tests, but I'll give it a shot.

If you want only the companyname, try

SELECT A.fname, A.lname, MAX(L.CompanyName)
FROM vwPersonAddress AS A
INNER JOIN vwLicCo AS L
ON L.personID = A.personID
GROUP BY A.personID, A.fname, A.lname

If you need the licencenumber as well:

SELECT A.fname, A.lname, L.CompanyName, L.licNum
FROM vwPersonAddress AS A
INNER JOIN vwLicCo AS L
ON L.personID = A.personID
WHERE NOT EXISTS
(SELECT *
FROM vwLicCo AS L2
WHERE L2.personID = L.personID
AND L2.CompanyName > L.CompanyName)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 10 Dec 2004 20:44:49 +0100, Hugo Kornelis
<hugo@.pe_NO_rFact.in_SPAM_fo> wrote:

>On Fri, 10 Dec 2004 12:24:16 -0700, csk wrote:
>>Hopefully someone will have some ideas on how to do this. I'm
>>officially stumped.
>>
>>I have two entities to join. Simplified descriptions follow:
>Hi csk,
>In the future, please post actual CREATE TABLE scripts, including all
>constraints. And add in some sample data (as INSERT statements) plus
>expected output, to clarify what you mean and to enable easy testing.
>See www.aspfaq.com/5006

My apologies, I should've looked for the FAQ first.
I was attempting to distill a much more complex problem into something
a bit more simple.

In retrospect, my example wasn't close enough to what I want to do,
but you've given me an idea to mull over. If it doesn't work I'll be
back with more pertinent/useful info.

Thank you!|||>> Any thoughts? <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

You also need to read ISO-11179 so you will stop putting those silly
"vw-' prefixes on VIEW names. What does a Volkswagen have to do with
this table??

Why is a license id totally different from a mere license number?
Surely you know better than to use IDENTITY or some other nonrelational
exposed physical locator in a real table.

CREATE TABLE PersonnelLocations
(ssn CHAR(9) NOT NULL -- or other industry standard
REFERENCES Personnel (ssn)
ON UPDATE CASCADE
ON DELETE CASCADE,
san INTEGER NOT NULL, san = Standard Address Number
REFERENCES Addresses (san)
ON UPDATE CASCADE,
...
PRIMARY KEY (ssn, san) );

CREATE TABLE LicenseHolders
(ssn CHAR(9) NOT NULL,
license_nbr INTEGER NOT NULL,
PRIMARY KEY (ssn, license_nbr)
company_name VARCHAR (35) DEFAULT '{{not a company}}' NOT NULL
...);

>> I want to join them - but only get the first or max company name
from entity 2 for a given person. In other words, the customer doesn't
care WHAT company name I put in the output, as long as it's only 1
(doesn't create extra records [sic]) and belongs to that
person. OH - and to keep it interesting, not every person will have a
company name in that second table... <<

You did not tell us what you want as output. Names? License numbers?
Also, rows are not records -- huge differences. Maybe this?

SELECT P.last_name, P.first_name, H.license_nbr, MAX(H.company_name)
FROM Personnel AS P
LEFT OUTER JOIN
LicenseHolders AS H
ON P.ssn = H.ssn
GROUP BY P.last_name, P.first_name, H.license_nbr;