2つのテーブルからのデータ取得および件数の取得

質問

こんばんは。お世話になっております。
ココのところ、件数を取得する質問ばかりですが、スキル不足故にこれまでの有難いアドバイスを応用出来ずにお恥ずかしい限りですが、改め今回もご指導いただければと投函させて頂きます。
イメージ的にミクシィのマイミク一覧の各々のマイミク件数の表示と同じような事がしたいと考えてます。（MySQL 4.1）

実際のテーブルとは違いますが、マイミクを例に取ったので・・

table：member
id　name
1　哲也
2　淳二
3　真由美
4　弘子

table：friend（マイミク登録状況を示すテーブル）
no　id　f_id
1　　2　　3
2　　1　　2
3　　3　　1
4　　4　　2
5　　2　　1
6　　4　　1
7　　4　　3
8　　2　　4

※id、f_id共にmemberテーブルのidと紐付け

そこで、テーブルfriendのidを4という条件で検索した時、
その4の相手方であるf_idをmemberテーブルのnameと共に一覧表示し、かつ、テーブルfriendにおいて、そのf_idをidに差し替え、相手方のf_idの件数を同時に表示させたいと考えてます。
上記テーブルの場合、以下のような感じ。

淳二　　3
哲也　　1
真由美　1

冒頭にも述べましたが、これまで多くのアドバイスを頂戴しながらも、似たような質問にてお恥ずかしい限りですが、描いている構想を進めるたびに壁に突き当たっている次第です。お忙しい中恐縮ですが、ご指導いただければ幸いに思います。宜しくお願い申し上げます。

回答No.5

chukenkenkou · ベストアンサー

検索結果が、0件になっていますか？

PHPには詳しくないですが、SQL中の変数は、「'」（単一引用符）で囲む必要があると聞いたことがあり、ネット上に公開されている例を見てもそうなっています。

where id = $id
↓
where id = '$id'

とすればどうでしょうか？
また、どの部分に問題があるかは、#1で示したようにSQLを分解して確認するといいと思いますよ。

お礼

chukenkenkou様

こんばんは。早速のお返事を有難う御座います。
先にお伝えしたSQLで検索結果は正常に呼び出されております。

where id = '$id'
に関しても、ご指摘のようにも幾度か試しておりましたが、該当のカラムが数値型の場合（$idが数値の場合？）、特に問題がないようです。
（'で括った方が尚良いことにはなりますね。）

>また、どの部分に問題があるかは、#1で示したようにSQLを分解して確認するといいと思いますよ。

そうですね。当初順を追っての説明を頂いているのでその説明を参考に、改めて見直してみたいと思います！
それでは、また改めてお返事（ご報告）いたしますので、宜しくお願い申し上げます。

補足

chukenkenkou様

こんばんは。お世話になっております。
あれから試行錯誤を繰り返しておりますが、未だf_idを取得できずにおり、PHP側にて最初に抽出すべくf_idを配列に入れ、その後、chukenkenkou様からご指導いただきましたSQLにて呼び出したデータに付加する形で、とりあえずの結果を得ることが出来ました。

ソース的にはこんな感じ。
$sql = "SELECT no, id, f_id FROM friend where id = '$id' order by no desc";
$result = mysql_query($sql);
$rows = mysql_num_rows($result);
while($row = mysql_fetch_array($result)){
$f_no[] = $row["f_id"];//f_idを配列に格納
}

～中略～

$sql ="select `name`, `count`
from `member` as m
inner join(select min(no) as `no`, id, f_id, count(f_id) as `count`
from `friend`
where id in(select f_id
from `friend`
where id = '$id')
group by id) as x
on m.id = x.id
order by no desc";
$result = mysql_query($sql);
$rows = mysql_num_rows($result);

$count_id = 0;

while($row = mysql_fetch_array($result)){
$count_no = $count_id++;
$f_id = $f_no[$count_no];

～以下省略～

本来であれば、全てSQLで処理すべきなのでしょうが、このようなソースで結果を得ることが出来ましたが、検索する際の効率？を考えると（自分のスキル不足から来る場当たり的な対処により）、データが多くなってきたときに不安を感じます。

chukenkenkou様からして、以上のようなソースを見て何かご指摘はありますでしょうか？
度重なる質問で恐縮ですがご意見のほど宜しくお願い申し上げます。

回答No.4

chukenkenkou

>お礼してから3日（現在11/30 22:36）ほど経っていますが、未だ解決しておりません。
>これ以上の分かりやすい説明はない！といった声が聞こえてきそうですが、はまりにはまって描いている結果を得られる気配すら感じない

最新のSQLは、どういう記述になっているのか教えてください。
また、テストで使用しているデータは、最初の質問時と同じでしょうか？もし、違っているなら、最新のテストデータを教えてください。
また、どういう結果を期待しているのに、現状のSQLではどうなるのかも、できれば教えてください。

お礼

chukenkenkou様

こんばんは。お世話になっております。早速のお返事を有難う御座います！

テストで行っているテーブルはカラム数こそ違いますが、質問時と同名のカラムで行っています。

以下は、現在のSQLです。抽出したいデータは4つなのですが、f_idだけが、（現時点で）何をやっても抽出できない状況です。
（上手くいかずに修正しては元に戻すの繰り返しで、当初お教えいただいたSQLとほぼ同じです。）

$sql ="select `name`, `image`, `count`
from `member` as m
inner join(select min(no) as no, id, f_id, count(f_id) as `count`
from `friend`
where id in(select f_id
from `friend`
where id = $id)
group by id) as x
on m.id = x.id
order by no desc ";
～中略～
echo "<p>".$row["f_id"]."/".$row["image"]."/".$row["name"]."/".$row["count"]."</p>";

補足

chukenkenkou様

こんにちは。お世話になっております。
先日開示したスクリプトに多少の不安はありますが、当初ご質問させていただいた結果は、chukenkenkou様のアドバイスにより得ることが出来ましたので、解決済みとさせていただきます。
ありがとう御座いました。

回答No.3

chukenkenkou

#2回答で、一部、不正確な回答をしてしまいました。

friend表のno列は、group byに指定しないため、min(no)あるいはmax(no)といった指定をしないと、MySQLでは結果は不定になってしまいます。他のRDBMSでは、エラーになります。
http://dev.mysql.com/doc/refman/4.1/ja/group-by-hidden-fields.html?ff=nopfpls

select `name`,友人数
from `member` as m
inner join(select min(no) as no,id,count(f_id) as 友人数
from `friend`
where id in(select f_id
from `friend`
where id=4)
group by id) as x
on m.id=x.id
order by no desc
;

お礼

chukenkenkou様

こんばんは。早速のお返事、並びにご丁寧なご説明を有難う御座います。
取り急ぎご挨拶まで。また改めてご報告させて頂きます。
貴重なお時間を割いてしまっていることに感謝いたします。

補足

chukenkenkou様

こんばんは、お世話になっております。
あれからchukenkenkou様のアドバイスのもと、今回から初めて行っているサブクエリの理解を深めながら、描いている結果を得られればと、試行錯誤を繰り返していますが、依然、f_idを得ることが出来ない状態です。
この場において、解決できないところに恥ずかしい気持ちもありますが、更なるヒントでも頂戴出来れば幸いです。宜しくお願いいたします。

回答No.2

chukenkenkou

集計（集合）関数、列の数値・文字演算などを行うと、RDBMSから返却される列名は、RDBMS独自のすごく長い名前、しかも特殊記号を含んでいたりで、アプリケーション側から参照するには、「as 別名」の別名で参照した方が簡単です。「as」は、後から標準SQLで規定されたこともあり、省略できるRDBMSが多いです。

「count(f_id) as 友人数」も、その外側のクエリ、あるいはアプリケーションでの参照ができるようにするため、別名として「友人数」を付けています。

>friendテーブルのf_idも出す

f_idは、memberのidと同一値ですよね。したがって、以下のクエリでは、member表のid列を、selectの選択リストに追加するだけです。

select m.id,`name`,友人数
from `member` as m
inner join(select id,count(f_id) as 友人数
from `friend`
where id in(select f_id
from `friend`
where id=4)
group by id) as x
on m.id=x.id
order by 友人数 desc,m.id
;

もし、これがfriend表から引き継ぐ必要がある列なら、インラインビュー（xという名前を付けている）のselectの選択リストに追加することになります。

friend表のno列でソートしたいとなれば、その列を一番外側のクエリまで引き継ぐ必要があります。

select `name`,友人数
from `member` as m
inner join(select no,id,count(f_id) as 友人数 -- ここでno列を指定
from `friend`
where id in(select f_id
from `friend`
where id=4)
group by id) as x
on m.id=x.id
order by no desc -- ここでno列、あるいはx.noといった名称で指定可能
;

お礼

chukenkenkou様

こんばんは。お世話になっております。
最後に補足欄にてお礼してから3日（現在11/30 22:36）ほど経っていますが、未だ解決しておりません。
これ以上の分かりやすい説明はない！といった声が聞こえてきそうですが、はまりにはまって描いている結果を得られる気配すら感じないのが正直なところです。お忙しい中恐縮ですが、今一度ご指導頂戴出来れば幸いです。

回答No.1

chukenkenkou

順を追って、クエリを作っていきましょう。

(1)テーブルfriendのidを4という条件で検索、その4の相手方であるf_idを得る

select f_id
from `friend`
where id=4
;

member表のname列への変換は、一番最後にしておきましょう。

(2)テーブルfriendにおいて、そのf_idをidに差し替え、相手方のf_idの件数
(1)のクエリを、サブクエリで使用しています。

select id,count(f_id) as 友人数
from `friend`
where id in(select f_id
from `friend`
where id=4)
group by id
;

(3)memberテーブルのname列で変換
(2)のクエリをインラインビュー内で使用しています。
表示順をどうしたいのか不明だったので、友人数の多い順、友人数が同じならmember表のid順としています。

select `name`,友人数
from `member` as m
inner join(select id,count(f_id) as 友人数
from `friend`
where id in(select f_id
from `friend`
where id=4)
group by id) as x
on m.id=x.id
order by 友人数 desc,m.id
;

お礼

chukenkenkou様

こんばんは。お世話になっております。
順を追っての詳しい説明を有難う御座います！

アドバイスいただける内容は全て感謝しておりますが、
このような順を追っての説明には、本当ありがたく感じております。

そこで、大変お恥ずかしい質問なのですが、コード内にある「友人数」とは、どのようなものになるのでしょうか？

説明のみならず、コードの記載も有難く思っている次第ではありますが、この説明を理解しながら進めてみたいと思っており、お返事にも多少のお時間をいただくかと思いますので、ご理解いただくと同時に、今一度ご説明いただければ幸いに思います。

補足

chukenkenkou様

度々すみません。先ほどの「友人数」は分かりました。お騒がせしました。

そこで、当初の質問で、検索結果を出す際、以下のように、friendテーブルのf_idも出すにはどのようになるのでしょうか？

f_id　name　count
2　　淳二　　3
1　　哲也　　1
3　　真由美　1

今回のサブクエリなるもの、初めて使うことになり、このf_idの出すタイミング？（コードの記載位置？）によって理解出来るような気がしてきました。

それと、
order by 友人数 desc,m.id
ですが、friendテーブルのnoの降順にするには、どのようになるのでしょうか？
この、「友人数の多い順、友人数が同じならmember表のid順」というのも大変貴重で「なるほど・・」とは思いつつ、ただ単にorder by no desc とやっても上手くいかないもので・・・。

お忙しい中、お手数お掛けし申し訳ありませんが、宜しくお願い申し上げます。