SQL question... - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

SQL question...

SQL question...# Database - 数据库

s*i2003-02-13 08:02

1 楼

I use MySQL and got this question...
there are two tables, one with ID and corresponding name.
the other table has two columns of IDs, id1 and id2.
I need to write a query to get both names for id1 and id2...

xt2003-02-13 08:02

2 楼

Any detail please?

j*i2003-02-13 08:02

3 楼

I am not familiar with MYSQL,
but I guess in Access,oracle etc you can do something like this:
select T1.Name as Name1,T3.ID1,T3.ID2,T3.name as Name2 from T1,(select * from
T2,T1 where T1.ID=T2.ID2) T3 where T1.id=t3.id1
/
You can add left or outer join according to the needs.
Basically first join on the first ID -"Translate one ID "
and use the query as Table.
Then join on second ID and got the second name.
Am I right?

【在 s**********i 的大作中提到】

: I use MySQL and got this question...
: there are two tables, one with ID and corresponding name.
: the other table has two columns of IDs, id1 and id2.
: I need to write a query to get both names for id1 and id2...

b*e2003-02-13 08:02

4 楼

don't understand your question. Do you mean you
want to get
name1,name2
name1,name2
...
name1,name2
for all the rows of table 2?
let me try:
select T1.name, T2.name from table1 as T1, table1 as T2, table2 as T3
where T1.ID = T3.Id and T2.Id=T3.ID
let me know if this works.

【在 s**********i 的大作中提到】

k*d2003-02-13 08:02

5 楼

Select T2.id1, T1.Name, T2.id2, T3.Name
From Table1 T1, Table2 T2, Table1 T3
Where T2.id1=T1.ID and T2.id2=T3.ID

【在 s**********i 的大作中提到】

b*e2003-02-13 08:02

6 楼

hehe, same as mine.

【在 k*******d 的大作中提到】

:
: Select T2.id1, T1.Name, T2.id2, T3.Name
: From Table1 T1, Table2 T2, Table1 T3
: Where T2.id1=T1.ID and T2.id2=T3.ID

s*i2003-02-13 08:02

7 楼

great. thanks a lot!

【在 b*e 的大作中提到】

: hehe, same as mine.

s*i2003-02-13 08:02

8 楼

I guess you probably right... but MySQL doesn't support
sub query thus your answer doesn't work on it...

【在 j**i 的大作中提到】

:
: I am not familiar with MYSQL,
: but I guess in Access,oracle etc you can do something like this:
: select T1.Name as Name1,T3.ID1,T3.ID2,T3.name as Name2 from T1,(select * from
: T2,T1 where T1.ID=T2.ID2) T3 where T1.id=t3.id1
: /
: You can add left or outer join according to the needs.
: Basically first join on the first ID -"Translate one ID "
: and use the query as Table.
: Then join on second ID and got the second name.

xt2003-02-13 08:02

9 楼

so if it has stored procedure you might want to use it.
No offense to open source projects, but I don't think MySQL
is really a good DBMS.

【在 s**********i 的大作中提到】

:
: I guess you probably right... but MySQL doesn't support
: sub query thus your answer doesn't work on it...

s*i2003-02-13 08:02

10 楼

it's not, but it's free... :)
I've heard the coming version would have sub query support though.

【在 xt 的大作中提到】

:
: so if it has stored procedure you might want to use it.
: No offense to open source projects, but I don't think MySQL
: is really a good DBMS.

xt2003-02-13 08:02

11 楼

I don't worry about the price, since I can always get it free
from the house. hehe

【在 s**********i 的大作中提到】

:
: it's not, but it's free... :)
: I've heard the coming version would have sub query support though.

f*o2003-02-13 08:02

12 楼

postgres.
mysql is supposed to be simple

【在 xt 的大作中提到】

:
: I don't worry about the price, since I can always get it free
: from the house. hehe

s*i2003-02-13 08:02

13 楼

yes. the point of MySQL is trade features for performance.

【在 f***o 的大作中提到】

:
: postgres.
: mysql is supposed to be simple

a*i2003-02-13 08:02

14 楼

Use postgresql, light years ahead of that POS called MySQL. :)

【在 s**********i 的大作中提到】

:
: yes. the point of MySQL is trade features for performance.

s*i2003-02-13 08:02

15 楼

performance is much worse than MySQL...

【在 a*****i 的大作中提到】

: Use postgresql, light years ahead of that POS called MySQL. :)

a*i2003-02-13 08:02

16 楼

Maybe you didn't tune it right?
I am not very familiar with MySQL though, but postgresql has quite some
spaces to fine-tune.

【在 s**********i 的大作中提到】

:
: performance is much worse than MySQL...

s*i2003-02-13 08:02

17 楼

MySQL is very fast in simple selects, and low cost
for connecting. this makes it one of the best choice
for web applications... there are some major companies
use MySQL for their web servers like yahoo, slashdot...

【在 a*****i 的大作中提到】

: Maybe you didn't tune it right?
: I am not very familiar with MySQL though, but postgresql has quite some
: spaces to fine-tune.