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
Webboy2008Asked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
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
 
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.