poulsborv
asked on
Help eliminate dups in a view
Hi I have a view that is pretty heavy and complicated... but for the most part it just joins a contacts table and an inventory table... the problem is that on person may own more than one coach, but in the resulting view I only want to see... lets say the most recent coach, do I have to create a view off of my inventory table that filters them out?
select contacts.*, inventory.* from contacts
inner join inventory on contacts.contactID = inventory.contactID
inner join (select max(uniqueID) uniqueID from inventory group by contactID, inventoryID) tmp
on tmp.inventoryID = inventory.inventoryID
In this query I'm assuming inventoryID is the code for each piece of inventory. All coaches would have the same number, all of each item would have the same number. This would get the most recent of each piece of inventory for each person.
inner join inventory on contacts.contactID = inventory.contactID
inner join (select max(uniqueID) uniqueID from inventory group by contactID, inventoryID) tmp
on tmp.inventoryID = inventory.inventoryID
In this query I'm assuming inventoryID is the code for each piece of inventory. All coaches would have the same number, all of each item would have the same number. This would get the most recent of each piece of inventory for each person.
ASKER
CREATE VIEW dbo.vContactsAndCoaches
AS
SELECT dbo.Inventory.StockNo, dbo.Inventory.[Year], dbo.Inventory.Make, dbo.Inventory.Model, dbo.Inventory.ModelNo, dbo.Inventory.Loc,
dbo.Inventory.Retail, dbo.Inventory.SerialNo, dbo.Inventory.SoldDate, dbo.Inventory.Body, dbo.Locations.Location, dbo.LookingFor.MinPrice,
dbo.LookingFor.MaxPrice, dbo.LookingFor.Financing, dbo.States.StateAndAbr, dbo.Contacts.FirstName, dbo.Contacts.MiddleName,
dbo.Contacts.LastName, dbo.Contacts.Salutation, dbo.Contacts.SpouseFirstNa me, dbo.Contacts.SpouseMiddleN ame, dbo.Contacts.SpouseLastNam e,
dbo.Contacts.SpouseSalutat ion, dbo.Contacts.Address, dbo.Contacts.City, dbo.Contacts.State, dbo.Contacts.Zip, dbo.Contacts.Country,
dbo.Contacts.County, dbo.Contacts.Address2, dbo.Contacts.City2, dbo.Contacts.State2, dbo.Contacts.Zip2, dbo.Contacts.Country2,
dbo.Contacts.HomePhone, dbo.Contacts.WorkPhone, dbo.Contacts.CellPhone, dbo.Contacts.Fax, dbo.Contacts.PhoneExt, dbo.Contacts.Email,
dbo.Contacts.Email2, dbo.Contacts.DateEntered, dbo.Contacts.LastUpdated, dbo.Contacts.ReferredBy, dbo.Contacts.LocationID,
dbo.Contacts.LastResult, dbo.Contacts.MergeDate, dbo.Contacts.[TimeStamp], dbo.Contacts.Origin, dbo.Contacts.Birthday,
dbo.Contacts.SpouseBirthda y, dbo.Contacts.Anniversary, dbo.Contacts.Picture, dbo.Contacts.InterestedinI D, dbo.Contacts.Title, dbo.Contacts.Company,
dbo.Contacts.LastResults, dbo.Contacts.TradeIn, dbo.Contacts.Confirmed, dbo.Contacts.Salesperson, dbo.Contacts.Salesperson2,
dbo.Contacts.ContactID, Users_1.FullName AS EnteredBy, Users_2.FullName AS UpdatedBy, dbo.Contacts.FullName AS ContactFullName,
Users_3.FullName AS SalespersonFull, dbo.Contacts.heard, dbo.Contacts.BC, Users_4.FullName AS SalespersonFull2
FROM dbo.Users Users_3 RIGHT OUTER JOIN
dbo.Contacts LEFT OUTER JOIN
dbo.Users Users_4 ON dbo.Contacts.Salesperson2 = Users_4.UserID ON Users_3.UserID = dbo.Contacts.Salesperson LEFT OUTER JOIN
dbo.Users Users_2 ON dbo.Contacts.UpdatedBy = Users_2.UserID LEFT OUTER JOIN
dbo.Users Users_1 ON dbo.Contacts.EnteredBy = Users_1.UserID LEFT OUTER JOIN
dbo.States ON dbo.Contacts.State = dbo.States.StateAbr LEFT OUTER JOIN
dbo.LookingFor ON dbo.Contacts.ContactID = dbo.LookingFor.ContactID LEFT OUTER JOIN
dbo.Locations ON dbo.Contacts.LocationID = dbo.Locations.LocationID LEFT OUTER JOIN
dbo.Inventory ON dbo.Contacts.ContactID = dbo.Inventory.ContactID
This is the statement as it stands currently.. the stockno on the vehicle is the primaryId for the inventory table
AS
SELECT dbo.Inventory.StockNo, dbo.Inventory.[Year], dbo.Inventory.Make, dbo.Inventory.Model, dbo.Inventory.ModelNo, dbo.Inventory.Loc,
dbo.Inventory.Retail, dbo.Inventory.SerialNo, dbo.Inventory.SoldDate, dbo.Inventory.Body, dbo.Locations.Location, dbo.LookingFor.MinPrice,
dbo.LookingFor.MaxPrice, dbo.LookingFor.Financing, dbo.States.StateAndAbr, dbo.Contacts.FirstName, dbo.Contacts.MiddleName,
dbo.Contacts.LastName, dbo.Contacts.Salutation, dbo.Contacts.SpouseFirstNa
dbo.Contacts.SpouseSalutat
dbo.Contacts.County, dbo.Contacts.Address2, dbo.Contacts.City2, dbo.Contacts.State2, dbo.Contacts.Zip2, dbo.Contacts.Country2,
dbo.Contacts.HomePhone, dbo.Contacts.WorkPhone, dbo.Contacts.CellPhone, dbo.Contacts.Fax, dbo.Contacts.PhoneExt, dbo.Contacts.Email,
dbo.Contacts.Email2, dbo.Contacts.DateEntered, dbo.Contacts.LastUpdated, dbo.Contacts.ReferredBy, dbo.Contacts.LocationID,
dbo.Contacts.LastResult, dbo.Contacts.MergeDate, dbo.Contacts.[TimeStamp], dbo.Contacts.Origin, dbo.Contacts.Birthday,
dbo.Contacts.SpouseBirthda
dbo.Contacts.LastResults, dbo.Contacts.TradeIn, dbo.Contacts.Confirmed, dbo.Contacts.Salesperson, dbo.Contacts.Salesperson2,
dbo.Contacts.ContactID, Users_1.FullName AS EnteredBy, Users_2.FullName AS UpdatedBy, dbo.Contacts.FullName AS ContactFullName,
Users_3.FullName AS SalespersonFull, dbo.Contacts.heard, dbo.Contacts.BC, Users_4.FullName AS SalespersonFull2
FROM dbo.Users Users_3 RIGHT OUTER JOIN
dbo.Contacts LEFT OUTER JOIN
dbo.Users Users_4 ON dbo.Contacts.Salesperson2 = Users_4.UserID ON Users_3.UserID = dbo.Contacts.Salesperson LEFT OUTER JOIN
dbo.Users Users_2 ON dbo.Contacts.UpdatedBy = Users_2.UserID LEFT OUTER JOIN
dbo.Users Users_1 ON dbo.Contacts.EnteredBy = Users_1.UserID LEFT OUTER JOIN
dbo.States ON dbo.Contacts.State = dbo.States.StateAbr LEFT OUTER JOIN
dbo.LookingFor ON dbo.Contacts.ContactID = dbo.LookingFor.ContactID LEFT OUTER JOIN
dbo.Locations ON dbo.Contacts.LocationID = dbo.Locations.LocationID LEFT OUTER JOIN
dbo.Inventory ON dbo.Contacts.ContactID = dbo.Inventory.ContactID
This is the statement as it stands currently.. the stockno on the vehicle is the primaryId for the inventory table
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
post the ddl structures for the contacts and inventory tables (and any others)
and specify how they should be joined to get the "latest" coach..