Plz, what is the most "correct" method

[PipHans]

Hi,

Given 2 tables:

Table1:
id Auto,int,PrimKe y
table2id int
txt nvarchar50

Table2:
id Auto,int,PrimKe y
order int

The scenario: Table2.order defines how the table1.txt is should be ordered.
Table1.table2id contains the id of the order. This cannot be changed :(

How do I select all Table1.txt values but ordered by their corresponding
values of the table2.order field?

--
Thx,
PipHans


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.516 / Virus Database: 313 - Release Date: 01-09-2003

[John Bell]

Re: Plz, what is the most "correct&q uot; method

Hi

The following should do what you require:

SELECT T1.id, T1.txt
FROM Table1 T1 JOIN Table2 T2 ON T1.Table2Id = T2.id
ORDER BY T2.order
John

"PipHans" <piphans@hotmai l.co> wrote in message
news:3f5f47c2$0 $48896$edfadb0f @dtext02.news.t ele.dk...[color=blue]
> Hi,
>
> Given 2 tables:
>
> Table1:
> id Auto,int,PrimKe y
> table2id int
> txt nvarchar50
>
> Table2:
> id Auto,int,PrimKe y
> order int
>
> The scenario: Table2.order defines how the table1.txt is should be[/color]
ordered.[color=blue]
> Table1.table2id contains the id of the order. This cannot be changed :(
>
> How do I select all Table1.txt values but ordered by their corresponding
> values of the table2.order field?
>
> --
> Thx,
> PipHans
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.516 / Virus Database: 313 - Release Date: 01-09-2003
>
>[/color]

[PipHans]

Re: Plz, what is the most &quot;correct&q uot; method

John Bell wrote:[color=blue]
> SELECT T1.id, T1.txt
> FROM Table1 T1 JOIN Table2 T2 ON T1.Table2Id = T2.id
> ORDER BY T2.order[/color]

It complains about the from-clause being wrong.
If I add "LEFT JOIN" instead of "JOIN", it runs like it should. (thx btw :)

Can you explain why it didnt run with just "join"?

--
Pip


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.516 / Virus Database: 313 - Release Date: 01-09-2003

[John Bell]

Re: Plz, what is the most &quot;correct&q uot; method

Hi

A LEFT JOIN would be all rows in the left hand side table (Table1) with any
matching rows in the right hand table (Table2) therefore an entry in Table2
does not have to exist. If an entry in Table2 does not exist then NULL
values are returned.

JOIN (or INNER JOIN) is where both tables have to contain the joined data.

As you didn't post the offending query or the actual error message, the only
thing I can imaging is some other syntax error:

SELECT T1.id, T1.txt
FROM Table1 T1 LEFT JOIN Table2 T2 ON T1.Table2Id = T2.id
ORDER BY T2.order

Should work OK.

John


"PipHans" <piphans@hotmai l.co> wrote in message
news:3f5f7147$0 $48899$edfadb0f @dtext02.news.t ele.dk...[color=blue]
> John Bell wrote:[color=green]
> > SELECT T1.id, T1.txt
> > FROM Table1 T1 JOIN Table2 T2 ON T1.Table2Id = T2.id
> > ORDER BY T2.order[/color]
>
> It complains about the from-clause being wrong.
> If I add "LEFT JOIN" instead of "JOIN", it runs like it should. (thx btw[/color]
:)[color=blue]
>
> Can you explain why it didnt run with just "join"?
>
> --
> Pip
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.516 / Virus Database: 313 - Release Date: 01-09-2003
>
>[/color]

[Erland Sommarskog]

Re: Plz, what is the most &quot;correct&q uot; method

PipHans (piphans@hotmai l.co) writes:[color=blue]
> John Bell wrote:[color=green]
>> SELECT T1.id, T1.txt
>> FROM Table1 T1 JOIN Table2 T2 ON T1.Table2Id = T2.id
>> ORDER BY T2.order[/color]
>
> It complains about the from-clause being wrong.
> If I add "LEFT JOIN" instead of "JOIN", it runs like it should.
> (thx btw :)
>
> Can you explain why it didnt run with just "join"?[/color]

Since what John suggested is legal syntax in SQL Server (save that
he should have put the last "order" in brackets), I suspect that
you are not using SQL Server, but some other DB engine.



--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[PipHans]

Re: Plz, what is the most &quot;correct&q uot; method

Erland Sommarskog wrote:[color=blue]
> Since what John suggested is legal syntax in SQL Server (save that
> he should have put the last "order" in brackets), I suspect that
> you are not using SQL Server, but some other DB engine.[/color]

True, I only tested on Access. - The SQL server cant be reached from home :)

--
/Pip


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.516 / Virus Database: 313 - Release Date: 01-09-2003

[PipHans]

Re: Plz, what is the most &quot;correct&q uot; method

John Bell wrote:[color=blue]
> Should work OK.[/color]

Yep. It worked on the SQL server at work...I only tested it on access
yesterday.

Thx :)

--
Pip


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.516 / Virus Database: 313 - Release Date: 01-09-2003

[Erland Sommarskog]

Re: Plz, what is the most &quot;correct&q uot; method

PipHans (piphans@hotmai l.co) writes:[color=blue]
> True, I only tested on Access. - The SQL server cant be reached from home[/color]

While both Access and SQL Server both claim to run SQL, there are
significant differences between the two - as there is about between
any pair of DBMSs.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp