HOBALL 筆記簿

July 16, 2009

MySQL Select with JOIN

Filed under: SQL — Tags: , , — hoball @ 6:20 pm

找回從前的提問,現記錄如下:

我想請教各位一個SQL Statement的寫法:
現在的db內,有兩個tables,裏面的data是相連的。
categories
——————
category_id
category_name
categories_relationship
—————————-
category_id
parent_id
例如:
Parent:
category_id = 1, category_name = Games, parent_id=0
category_id = 2, category_name = Newspaper, parent_id=0
Subcategory:
category_id = 20, category_name = RPG, parent_id = 1
category_id = 21, category_name = Appledaily, parent_id=2
在一個php內,我得到某category_id,
現在想用SQL把: category name 和 它的parent name 顯示出來
我知道可以用兩句SQL來達成,但是我想知道一句就可以完成的寫法是怎樣的
請問會是用幾個JOIN,還是會create temp table?

作者: icomefromhk    時間: 2009-1-19 19:59
select sc.category_name, pc.category_name from categories sc join categories_relationship cr on sc.category_id = cr.category_id join categories pc on pc.parent_id = cr.category_id


作者: icomefromhk    時間: 2009-1-19 20:02
其實你轉用一個table裝晒d parent-child relationship都得
即係
categories
——————
category_id
category_name
parent_id
咁樣用一個join就已經拎倒你要既野

 

另外,想請問一般在什麼請況下才會把data 和relationship分開?

作者: icomefromhk    時間: 2009-1-19 23:10
data modeling既野好難講…不過你呢個情況, "一般黎講", 除非category同subcategory要裝既information係唔同, 如果唔係, 用同一個table黎裝"應該"對query既performance好d


作者: alextamly    時間: 2009-1-19 23:40
多數單對單o既就唔好分table喇..單對多的話就睇情況…

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: