Solved

sql query using for asp

Posted on 2011-09-21
6
220 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Quer 4 23
Need return values from a stored procedure 8 23
Finding gaps or missing dates in a date range using TSQL 3 28
SQL View / Qtry 3 13
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

821 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