?
Solved

Help eliminate dups in a view

Posted on 2004-03-29
4
Medium Priority
?
193 Views
Last Modified: 2012-08-14
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?
0
Comment
Question by:poulsborv
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10706982
it depends ...

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

0
 
LVL 15

Expert Comment

by:SRigney
ID: 10707491
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.
0
 

Author Comment

by:poulsborv
ID: 10707777
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.SpouseFirstName, dbo.Contacts.SpouseMiddleName, dbo.Contacts.SpouseLastName,
                      dbo.Contacts.SpouseSalutation, 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.SpouseBirthday, dbo.Contacts.Anniversary, dbo.Contacts.Picture, dbo.Contacts.InterestedinID, 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
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 750 total points
ID: 10711317
This addition to the view may do it

CREATE VIEW dbo.vContactsAndCoaches
AS
SELECT     I.StockNo, I.[Year], I.Make, I.Model, I.ModelNo, I.Loc,
                      I.Retail, I.SerialNo, I.SoldDate, I.Body, L.Location, Look.MinPrice,
                      Look.MaxPrice, Look.Financing, S.StateAndAbr, C.FirstName, C.MiddleName,
                      C.LastName, C.Salutation, C.SpouseFirstName, C.SpouseMiddleName, C.SpouseLastName,
                      C.SpouseSalutation, C.Address, C.City, C.State, C.Zip, C.Country,
                      C.County, C.Address2, C.City2, C.State2, C.Zip2, C.Country2,
                      C.HomePhone, C.WorkPhone, C.CellPhone, C.Fax, C.PhoneExt, C.Email,
                      C.Email2, C.DateEntered, C.LastUpdated, C.ReferredBy, C.LocationID,
                      C.LastResult, C.MergeDate, C.[TimeStamp], C.Origin, C.Birthday,
                      C.SpouseBirthday, C.Anniversary, C.Picture, C.InterestedinID, C.Title, C.Company,
                      C.LastResults, C.TradeIn, C.Confirmed, C.Salesperson, C.Salesperson2,
                      C.ContactID, Users_1.FullName AS EnteredBy, Users_2.FullName AS UpdatedBy, C.FullName AS ContactFullName,
                      Users_3.FullName AS SalespersonFull, C.heard, C.BC, Users_4.FullName AS SalespersonFull2
FROM   dbo.Contacts as C
Left Outer Join  dbo.Users Users_3
  ON C.Salesperson  = Users_3.UserID
LEFT OUTER JOIN dbo.Users Users_4
  ON C.Salesperson2 = Users_4.UserID
LEFT OUTER JOIN dbo.Users Users_2
  ON C.UpdatedBy = Users_2.UserID
LEFT OUTER JOIN dbo.Users Users_1
  ON C.EnteredBy = Users_1.UserID
LEFT OUTER JOIN dbo.States as S
  ON C.State = S.StateAbr
LEFT OUTER JOIN dbo.LookingFor as look
  ON C.ContactID = Look.ContactID
LEFT OUTER JOIN dbo.Locations as L
  ON C.LocationID = L.LocationID
LEFT OUTER JOIN dbo.Inventory as I
  ON C.ContactID = I.ContactID
Where I.stockno = (select max(I1.stockno)
                     from dbo.Inventory as I1
                    Where I1.contactID=C.ContactID)
 

you didn't really explain how the latest vehicle should be selected.....
so i just picked the latest stockno for the contact.......

are you certain that all these joins need to be outer joins?

i'd certainly have though you'd only want to return contacts with inventory...    
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question