Solved

About SQL ( uses sql server 2000)

Posted on 2002-04-02
11
135 Views
Last Modified: 2010-04-04
table1
NameID  Name
1       aaaa
2       bbbb
3       cccc
4       dddd

table2
ID      NameID
1       1
1       2
2       3
3       4

Query result:
ID     AllName
1      aaaa bbbb
2      cccc
3      dddd
How to write this 'SQL' sentence ?



0
Comment
Question by:lingxin
  • 3
  • 3
  • 2
  • +3
11 Comments
 

Author Comment

by:lingxin
ID: 6916811
????
0
 
LVL 45

Expert Comment

by:aikimark
ID: 6917094
1. Do you need the result in a table or only as a query result?

2. Is the query result concatenation with a space character delimter.

===========================================
The simplest solution produces a table and requires 2 SQL statements.
0
 
LVL 10

Expert Comment

by:Jacco
ID: 6917325
You could try having a user defined function for that:

create function dbo.fncAllName(@NameID int)
returns @AllName varchar(256)
as
begin
  .. function code here ..
end

Then the query would be:

select
  ID, dbo.fncAllName(ID)
from
  table2
group by
  ID

I did not implement the function becaus it requires iterating a cursor an I can't remember exactly how to write that.

This solution will be somewhat slow though... Let me know if you want the function.

Regards Jacco

0
 
LVL 10

Expert Comment

by:Jacco
ID: 6917326
Oh yeah, just to know is two the maximum number of names combined or can there be more?
0
 

Author Comment

by:lingxin
ID: 6917375
to  Jacco
uses function ?
how writer
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 10

Assisted Solution

by:Jacco
Jacco earned 150 total points
ID: 6919389
I will try and make some working code tommorow. But the way you want to do this is not the way to go for a good relational database design. As an exercise in building function with SQL 2000 it is.

Regards Jacco
0
 
LVL 45

Accepted Solution

by:
aikimark earned 150 total points
ID: 6919472
query result tblAllName table:
ID LongInteger Unique
AllName VarChar

Insert Into tblAllName (ID, AllName)
Select table2.ID, table1.Name
From table1 Inner Join table2 on table1.NameID = table2.NameID;

Update tblAllName Inner Join table2 on tblAllName.ID = table2.ID Inner Join table1 on table1.NameID = table2.NameID  
Set AllName = AllName & " " & table1.name
Where table2.NameID Not In
(Select Min(I.NameID)
 From table2 As I
 Where I.ID = tblAllName.ID);
0
 

Author Comment

by:lingxin
ID: 6919887
go
0
 

Expert Comment

by:delphipal
ID: 6920441
select id,name from table1,table2 where
table1.nameid=table2.nameid order by id
0
 
LVL 4

Expert Comment

by:jsweby
ID: 6920815
Or you can use a join:

SELECT Table1.Name, Table2.ID
FROM Table1
INNER JOIN TABLE2 ON
(Table2.NameID = Table1.NameID)
0
 
LVL 1

Expert Comment

by:pnh73
ID: 9002862
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Split between aikimark and Jacco

Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
Paul (pnh73)
EE Cleanup Volunteer
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

895 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now