Problem for me ! - SQL Server Forums

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

Transact-SQL (2000)

Problem for me !

Author

Topic

sonix
Starting Member

2 Posts

Posted - 2004-11-24 : 11:04:07

Hi

I have two tables.

table 1 with the following fields:
- ID
- creationdate

table 2:
- ID
- title
- text
- FK_table1.ID
- FK_langauage_ID

Short description:
I create an entry in table 1, this entry could be an article in div. languages. (Maybe German,English,Spain ...)
The text relevant entries are storred in table two.

Now, what is the way to order the Select statement to get a result like:
table1.id
table1.creationdate
(table2.language_ID's)
(table2.titel's)

I need this because i want to create a list like:
created: title: languages:

What is the solution, or is this not possible with a single query?

Thanks in advance
THX

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-11-24 : 11:34:13

pls.....give us some sample input data and matching expected results....

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-24 : 13:33:54

table1:
- ID
- creationdate

table 2:
- ID
- title
- text
- FK_table1.ID<=

- FK_langauage_ID

Select   title, text, creationDate
From     table1 a inner join table2 b on
         a.id=b.id
order by creationDate,a.id

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
A candle loses nothing by lighting another candle

sonix
Starting Member

2 Posts

Posted - 2004-11-25 : 01:45:31

OK, it seems I wasn't exact.

Table one stores only the cration date of a text container,
the second table stores the title and text of these container.
The text could be of different languages.

If I try to get the results of all table one elements this way:
SELECT *
FROM table1
INNER JOIN table2 ON table1.ID = table2.table1_id

I get (a sample):

table1.ID: title: text: language: table2.ID:
1, "Test Title", "Test summary", 1 (English), 1
1, "Test Titel", "Test Beschrieb", 2 (German), 2

Now, how can I get the result like this:

table1.ID: title: text: language: table2.ID:
1, "Test Title", "Test summary", "1,2" , 1

-> only one row per table1.ID but with the related
text container (table2) integrated !!

And how can I sort it by title ?

I know it's a little confusing but I don't know how I can
explain it better :-/

Thanks for your help!

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-12-14 : 12:30:09

quote:
Originally posted by sonix

table1.ID: title: text: language: table2.ID:
1, "Test Title", "Test summary", 1 (English), 1
1, "Test Titel", "Test Beschrieb", 2 (German), 2

Now, how can I get the result like this:

table1.ID: title: text: language: table2.ID:
1, "Test Title", "Test summary", "1,2" , 1

Why would table2.ID be 1?
any way Search on cross tab or create a function de concatenate the language field and then you can use
something like

SELECT rID,title,[text],udfLanguage(1) FROM @t1 a INNER JOIN @t2 b ON a.rID = b.rID Where langage = 1

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
A candle loses nothing by lighting another candle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources