Retrieve Single Row from INNER JOINED table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Retrieve Single Row from INNER JOINED table

Author

Topic

Mr Fett
Starting Member

28 Posts

Posted - 2009-07-22 : 14:36:32

Hi all,

I'm not new to SQL but this is a basic thing I've never really cracked!

I have two tables, bookings and journeys - one booking contains multiple journeys (e.g. outward and return journey):

BOOKINGS
--------
bookingID
dateBooked

JOURNEYS
--------
journeyID
bookingID
departureDate
departurePlace
ArrivalPlace

I want to list all current forth coming bookings but want to show the journey info summarised for each booking. A straight forward inner join gives me a row per journey, not a row per booking.

Is there a way to compile the data from the journey table in to a single string?

So instead of:

bookingID datebooked journeyID departureDate departurePlace
1 12-jul-09 1 18-aug-09 Transylvania
1 12-jul-09 2 25-aug-09 Chicago

I get

bookingID datebooked journeySummary
1 12-jul-09 18-aug-09(Transylvania), 25-aug-09(Chicago)

Thanks all!

Bob

weipublic
Starting Member

19 Posts

Posted - 2009-07-22 : 21:36:15

Create a function which takes a parameter as bookingID and returns the string you want (eg. 18-aug-09(Transylvania), 25-aug-09(Chicago)). And then using

SELECT *, departureSummary = dbo.functionName(bookingID)
FROM BOOKINGS

Let me know if you need the details of the function.

By the way if someone has better way to do it. Please advise.

Wei

Mr Fett
Starting Member

28 Posts

Posted - 2009-07-23 : 09:20:24

Hi Wei,

Thanks for the response - its much appreciated!

Is a function the same as a stored procedure?

If you could give me an example that would be great!!

Apologies for my lack of knowledge on this!

Cheers

Bob

weipublic
Starting Member

19 Posts

Posted - 2009-07-23 : 09:45:31

Hi Bob,

A function is different from a stored procedure. Please try following, it should work.

CREATE FUNCTION GetSummary
(
@bookingID INT
)
RETURNS NVARCHAR(255)
AS
BEGIN
DECLARE @summary AS NVARCHAR(255)
SELECT @summary = COALESCE(@summary + ', ', '') + CONVERT (nvarchar(11) , departureDate,101 ) + '(' + departurePlace + ')'
FROM JOURNEYS WHERE bookingID=@bookingID
RETURN @summary
END

And then, using following to get the results

SELECT *, departureSummary = dbo.GetSummary(bookingID)
FROM BOOKINGS

Thanks,
Wei

Mr Fett
Starting Member

28 Posts

Posted - 2009-07-23 : 10:38:09

Fantastic!

Thanks Wei - I'll try it out and post my response for others!

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-07-23 : 10:50:26

if you are using sql 2005 or later, you could use the PIVOT operator.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources