>> 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;