Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


sql query using for asp

Posted on 2011-09-21
Medium Priority
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.

                         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
Question by:Webboy2008
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
  • 3
  • 2
LVL 14

Expert Comment

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)?

Author Comment

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

Expert Comment

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.
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

LVL 14

Expert Comment

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.
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?
LVL 75

Accepted Solution

Anthony Perkins earned 2000 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,
        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')
        e.[Start Work],
        e.[End Work],
        e.[Company ID],

Open in new window


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

664 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