Solved

sql query using for asp

Posted on 2011-09-21
6
211 Views
Last Modified: 2012-05-12
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
Comment
Question by:Webboy2008
  • 3
  • 2
6 Comments
 
LVL 14

Expert Comment

by:mds-cos
ID: 36577301
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
 

Author Comment

by:Webboy2008
ID: 36577364
i just want to keep total record 9 un-change. and add first/last name.
0
 
LVL 14

Expert Comment

by:mds-cos
ID: 36577382
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 14

Expert Comment

by:mds-cos
ID: 36577400
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36577645
>>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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 36577663
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

831 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