Help with a Join - join only first or max?

**Hugo Kornelis** · Jul 23 '05, 07:14 AM

Re: Help with a Join - join only first or max?

On Fri, 10 Dec 2004 12:24:16 -0700, csk wrote:
[color=blue]
>Hopefully someone will have some ideas on how to do this. I'm
>officially stumped.
>
>I have two entities to join. Simplified descriptions follow:[/color]

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
[color=blue]
>The first has names and addresses (vwPersonAddres s) keyed by PersonID
>(it is actually a view on two tables, but it works exactly as I want
>it to, so all good there).
>
>vwPersonAddres s
>--------------------
>personID (PK)
>addrType (PK)
>fname
>lname
>addr1
>city
>st[/color]

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!

[color=blue]
>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)[/color]

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).

[color=blue]
>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...[/color]

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!

[color=blue]
>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.[/color]

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.CompanyNa me)
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)

**csk** · Jul 23 '05, 07:14 AM

Re: Help with a Join - join only first or max?

On Fri, 10 Dec 2004 20:44:49 +0100, Hugo Kornelis
<hugo@pe_NO_rFa ct.in_SPAM_fo> wrote:
[color=blue]
>On Fri, 10 Dec 2004 12:24:16 -0700, csk wrote:
>[color=green]
>>Hopefully someone will have some ideas on how to do this. I'm
>>officially stumped.
>>
>>I have two entities to join. Simplified descriptions follow:[/color]
>
>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[/color]

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!

**--CELKO--** · Jul 23 '05, 07:14 AM

Re: Help with a Join - join only first or max?

>> 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 PersonnelLocati ons
(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
...);
[color=blue][color=green]
>> I want to join them - but only get the first or max company name[/color][/color]
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_n ame)
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;

Help with a Join - join only first or max?

Help with a Join - join only first or max?

Comment

Comment

Comment