Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

sql query using for asp

Db Experts:

I have the following codes in sql script. and please view my attached db tables.
What I try to do is to add g.FirstName and g.LastName.

After adding it, it expands to 500+ record (The original result from the following query is 9).
My question is how can I add firstname and lastname without expands the records.
I know it may be because of the distinct but i can't remove it.



SELECT DISTINCT
                         d.Address AS toAddress, d.City AS toCity, d.Zip AS toZip, f.Address AS frAddress, f.City AS frCity, f.Zip AS frZip, g.MilesToWork, g.PickupAddressID, p.EmployerName,
                         g.VanpoolID, e.[Start Work] AS startwork, e.[End Work] AS endwork, e.
[Company ID] AS companyId
FROM            Vanpool AS p INNER JOIN
                         Passenger AS g ON p.VanpoolID = g.VanpoolID INNER JOIN
                         Address AS d ON p.DestinationID = d.AddressID INNER JOIN
                         Address AS f ON g.PickupAddressID = f.AddressID INNER JOIN
                         EmployerWorkHoursVanpoolDetail AS e ON p.VanpoolID = e.[Cust ID]
WHERE        (p.EmployerName = 'AAA')
ORDER BY frCity db.pdf
0
Webboy2008
Asked:
Webboy2008
  • 3
  • 2
1 Solution
 
mds-cosCommented:
I suppose the first question I have is if the results are correct.  Specifically, is each of the 500 results returned a distinct record (meaning all fields do not match)?  Based on the way you word question, and looking at your query, it sounds like you may be getting correct results (e.g. multiple contacts per address)?
0
 
Webboy2008Author Commented:
i just want to keep total record 9 un-change. and add first/last name.
0
 
mds-cosCommented:
Understand.  But do you have multiple first/last name pairs associated with each of the original 9 results?  If you do, the query is going to return each and every first/last name for each and every one of the 9 original results.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
mds-cosCommented:
Part of what I'm trying to understand here is the nature of your data and exactly what you want returned from it.  From what I can tell Vanpool.VanpoolID is in a one to many relationship with Passengers.VanpoolID.  If this is the case, you cannot return a single first/last name in your query because there is no single name.  There are multiple names for each vanpool.

That said, you could decide to return a single first / last name by putting a TOP into the querey.  But the data returned would be less than accurate.
0
 
Anthony PerkinsCommented:
>>and add first/last name. <<
You will need to define what you mean by "first".  Do you have a column that indicates which is the first?
0
 
Anthony PerkinsCommented:
If you do not have a column that indicates which is the first you can use a CTE with ROW_NUMBER() or something like the query below, but the one selected may not necessarily be the way you want.
SELECT  d.Address toAddress,
        d.City toCity,
        d.Zip toZip,
        f.Address frAddress,
        f.City frCity,
        f.Zip frZip,
        g.MilesToWork,
        g.PickupAddressID,
        p.EmployerName,
        g.VanpoolID,
        e.[Start Work] startwork,
        e.[End Work] endwork,
        e.[Company ID] companyId,
        MIN(g.FirstName + ' ' + g.LastName) PassengerName
FROM    Vanpool AS p
        INNER JOIN Passenger AS g ON p.VanpoolID = g.VanpoolID
        INNER JOIN Address AS d ON p.DestinationID = d.AddressID
        INNER JOIN Address AS f ON g.PickupAddressID = f.AddressID
        INNER JOIN EmployerWorkHoursVanpoolDetail AS e ON p.VanpoolID = e.[Cust ID]
WHERE   (p.EmployerName = 'AAA')
GROUP BY
        d.Address,
        d.City,
        d.Zip,
        f.Address,
        f.City,
        f.Zip,
        g.MilesToWork,
        g.PickupAddressID,
        p.EmployerName,
        g.VanpoolID,
        e.[Start Work],
        e.[End Work],
        e.[Company ID],
ORDER BY 
        f.City

Open in new window

0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now