simple join - havent done this for years!

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

SQL Server 2000 Forums

SQL Server Development (2000)

simple join - havent done this for years!

Author

Topic

petem
Starting Member

44 Posts

Posted - 2004-08-31 : 04:43:27

Hi all,

I've got extremely rusty on SQL over the last year or so and was wondering if I could have some help on what I think should be an easy query.

I have two tables:
car - regnumber, colour, enginesize, etc etc.
accessories - regnumber, accessory.

I need to return the car details from 'car' with a comma seperated list of accessories from 'accessories'.

At the moment Im getting the same number of rows as accessories - you know, the repeating of the car data. I cant remember how to sort this problem out....

ie: output looks like...
reg, blue, 1800, etc, etc, accessory1
reg, blue, 1800, etc, etc, accessory2
reg, blue, 1800, etc, etc, accessory3

could i put the accessories into a varable first or something?

As an afterthought - It's for an asp page. Am I better to output the accessories as 1 comma-seperated field or sort it all out on the page with asp?

Any help much appreciated,

Pete

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-08-31 : 04:50:35

Take a read through this list of articles. I KNOW one will give you an example of what you are trying to do. I'd give the the right article, but it's almost 4am here and I'm tired.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-08-31 : 04:55:17

sounds like you want to do a pivot or crosstab type query. Search on those terms in the forum and the main site and you will find plenty of examples.

-ec

petem
Starting Member

44 Posts

Posted - 2004-08-31 : 04:59:33

to be honest i dont really know how to search for this one...

which keywords do I need to use?

thanks,

Pete

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-08-31 : 05:14:45

If you want to return a single field containing a comma-delimited list of accessories (in addition to all fields from car), you may be best off using a user-defined function that takes reg as a parameter.
This would return a string built up using a loop.

Mark

petem
Starting Member

44 Posts

Posted - 2004-08-31 : 05:21:09

Cheers Mark,

Would this have a major hit on performance?

Pete

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-31 : 05:39:02

well this gives you CSV list...

Declare @List varchar(1000)
SELECT @List = COALESCE(@List + ', ', '') + column1
FROM MyTable

puting this in a function should return you a CSV string of desired values.

Go with the flow & have fun! Else fight the flow :)

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-08-31 : 07:15:14

I didn't know you could do that Spirit1 - that's very cool! Gotta go - got a few while loops to replace!!

Mark

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-31 : 07:17:22

heh... hope there's not to many of them... :)

curtosy of sqlteam.com

Go with the flow & have fun! Else fight the flow :)

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-08-31 : 16:48:59

Doh, I'm sorry!
I forgot to paste in the link to the CSV articles!!

http://www.sqlteam.com/searchresults.asp?SearchTerms=csv

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

petem
Starting Member

44 Posts

Posted - 2004-09-03 : 07:35:23

That is very cool Spirit :)

cheers,

Pete

petem
Starting Member

44 Posts

Posted - 2004-09-03 : 07:48:42

BTW: I now have another query:

I've created a small function (that uses Spirit's solution) whose job it is to get the accessories for each car returned from a stored procedure.


alter Function getRegNumberusedSearch (@regNo varChar(10))
	RETURNS varChar(10)
AS
BEGIN
Declare @List varchar(1000)
SELECT @List = COALESCE(@List + ', ', '') + accessory
FROM frxrep2.dbo.usedaccessories WHERE regnumber = @regno
return @List
END

My question is: How can I get this into the SQL statement in the StoredProcedure?
(The SPROC returns a large number of cars - I need the accessories for each car)

I've tried...


SQL SELECT string etc etc,
dbo.getRegNumberusedSearch(RegNumber1) AS AccessoryList
FROM table INNER JOIN etc etc.

uummm. Again, thanks for any help,

Pete

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-03 : 10:25:45

so what exactly doesn't work with your query??

Go with the flow & have fun! Else fight the flow :)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-03 : 11:13:10

you're returning a varchar(10). are you sure that's going to be big enough? (I doubt it).

Also, i would add an ORDER BY to your string building SQL statement to keep things deterministic.

- Jeff

petem
Starting Member

44 Posts

Posted - 2004-09-03 : 11:47:26

I see what you mean about the size of the Return value


alter Function getRegNumberusedSearch (@regNo varChar(20))
	RETURNS varChar(1000)
AS
BEGIN
Declare @List varchar(1000)
SELECT @List = COALESCE(@List + ', ', '') + accessory
FROM frxrep2.dbo.usedaccessories WHERE regnumber = @regno ORDER BY Accessory
return @List
END

Do you how I can test this function.....

cheers,

Pete

petem
Starting Member

44 Posts

Posted - 2004-09-03 : 11:56:54

oh sorry - Im in a right mess (lots on)

The function is now working - just tried it in a new SELECT statement.

I think it's not working in the other statement because of some other reason --- If I find out Ill let you know - It's a MASSIVE statement and I didnt write it :)

Thanks for all the help,

Pete

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-03 : 11:57:08

how to test it?? run it on your data and see what it returns. You need to have an idea what it will return...

select dbo.Function getRegNumberusedSearch ('regnum')

or did u mean something else?

Go with the flow & have fun! Else fight the flow :)

petem
Starting Member

44 Posts

Posted - 2004-09-03 : 12:12:58

I managed to get it working with a SELECT statement that included a


WHERE regNo = 'sdfiosg'

Unfortunately, Where I want to use the function I do not know the reg number yet - It is within the same SELECT string.


SELECT regno, color, mileage, price, db.getRegNumberusedSearch(regno) FROM bla bla

but I dont know regno at the time of the SELECT as there are many returned...

does that make sense? Any ideas how I can work this...?

Pete

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-03 : 12:54:04

ok i don't get it... what is the error you're getting??
the select you have should work.

Go with the flow & have fun! Else fight the flow :)

petem
Starting Member

44 Posts

Posted - 2004-09-03 : 15:07:01

The problem is that I dont know what the regNo is at the point of calling the function. I only have the column name.

so db.getRegNumberusedSearch(regno)

is really

db.getRegNumberusedSearch(ColumnName)

Pete

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-03 : 15:25:46

but that's the point of the function. it can be called like that.

try this in northwind:

create Function GetOrderDetails (@OrderId int)
RETURNS varChar(1000)
AS
BEGIN
Declare @List varchar(1000)
SELECT @List = COALESCE(@List + ', ', '') + cast(ProductId as varchar(10))
FROM [Order Details] WHERE OrderId = @OrderId ORDER BY OrderId
return @List
END

go

select OrderId, dbo.GetOrderDetails(OrderId) as OrderDetails
from orders
where OrderId < '10270'

drop function GetOrderDetails

Go with the flow & have fun! Else fight the flow :)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources