access で結果の出るSQLが、MySQLではできなくて困っていま

質問

access で結果の出るSQLが、MySQLではできなくて困っています。

普段、ちょっと古いですが、Access2002を使用しています。

どうしても、Linuxサーバー上でデーターベースを利用する必要がでて、Fedora13に、mysql　5.1.48　をyamでインストールしました。

ところが、必要なSELECT検索が出来ず、困っています。

次のようなテーブル構造だと思ってください。

table A
id｜No
－－－－
a1｜01
b1｜11
c0｜20

table B

No｜option1｜option2
－－－－－－－－－－
01｜Ｌ　　　｜Red
01｜Ｓ　　　｜Red
01｜Ｌ　　　｜Green
01｜Ｓ　　　｜Green
11｜Ｓ　　　｜(値なし)
11｜Ｌ　　　｜(値なし)

このNoとオプション１、２から、IDを割り出したしたいのです。
オプション１，２は、存在していないデータもあります。

Accessなら、table A　の　No　と　table Bのoption1、option2　を　&で結んで、結合プロパティをtable Aの全レコードに指定したＳＱＬ

SELECT A.id, [A].[No] & [B].[option1] & [B].[option2] AS test
FROM A LEFT JOIN B ON A.[No] = B.[No]
WHERE ((([A].[No] & [B].[option1] & [B].[option2])="01LRED"));

とすれば、a1　と結果が返ります。
table B　にオプション値の登録のないものでも、

SELECT A.id, [A].[No] & [B].[option1] & [B].[option2] AS test
FROM A LEFT JOIN B ON A.[No] = B.[No]
WHERE ((([A].[No] & [B].[option1] & [B].[option2])="20"));

とすれば、C0が返ってくれます。

ところが、同じテーブル構成をMySQLに作成し、

SELECT A.id, A.No & B.option1 & B.option2 AS test FROM A LEFT JOIN B ON A.No = B.No WHERE A.No & B.option1 & B.option2 = "01LRED";

を入力してみましたが、結果が出ません。

エラーらしきものといえば、

Empty set, 5 warnings (0.00 sec)

が表示されています。

結合の方法が違うのかと思い、Googleで調べ

SELECT concat(A.id, A.No & B.option1 & B.option2) AS test FROM A LEFT JOIN B ON A.No = B.No WHERE (A.No & B.option1 & B.option2) = "01LRED";

と、concat　を使ってみましたが、だめでした。

MySQLで、上記のように複数のテーブル／フィールドを結合して、その値をもとに検索することはできないでしょうか？

よろしくお願いいたします。

回答No.2

chukenkenkou · ベストアンサー

＜例１＞
SELECT
A.id,CONCAT(A.No,COALESCE(B.option1,''),COALESCE(B.option2,'')) AS test
FROM A LEFT JOIN B ON A.No=B.No
WHERE CONCAT(A.No,COALESCE(B.option1,''),COALESCE(B.option2,''))='01LRED'
;

SELECT
A.id,CONCAT(A.No,COALESCE(B.option1,''),COALESCE(B.option2,'')) AS test
FROM A LEFT JOIN B ON A.No=B.No
WHERE CONCAT(A.No,COALESCE(B.option1,''),COALESCE(B.option2,''))='20'
;

＜例２＞インラインビュー（FROM句でのサブクエリ）を使えば、式の別名を条件式で使用可能。
　　　ただし、性能的には推奨できないし、ビュー表で定義できない点に注意
SELECT
x.id,test
FROM
(SELECT
A.id,CONCAT(A.No,COALESCE(B.option1,''),COALESCE(B.option2,'')) AS test
FROM A LEFT JOIN B ON A.No=B.No) AS x
WHERE x.test='01LRED'
;

SELECT
x.id,test
FROM
(SELECT
A.id,CONCAT(A.No,COALESCE(B.option1,''),COALESCE(B.option2,'')) AS test
FROM A LEFT JOIN B ON A.No=B.No) AS x
WHERE x.test='20'
;

(1)「&」は論理演算であり、MySQLの文字結合はCONCAT関数を使います。
(2)「値なし」がNULLなのか、''（長さ0の文字列）なのかで、単純に結合していいのかどうかが違ってきます。
　　提示したSQLでは、COALESCE関数でその部分を吸収しています。
(3)提示されたデータやSQLでは、大文字だったり小文字だったり、半角だったり全角だったりしています。
MySQLでは、大文字と小文字を同じ値として扱います。もし区別したいなら、BINARY属性にする必要があります。
半角文字と全角文字は別の値として扱われます。
(4)「'」（単一引用符）、「"」（二重引用符）、「`」（バッククォート）の扱い
「'」（単一引用符）で囲んだものは、文字定数。
　「"」（二重引用符）で囲んだものは、MySQLの構文モードにより扱いが違います。
標準SQLやMySQL以外の主要なRDBMSでは、表名や列名が予約語と被る場合や記号を含む場合などに名前を「"」で囲みます。
MySQLでは、インストール時の標準設定では「MySQL構文モード」となり、「"」で囲んだものは文字定数になります。
「ANSIモード」で起動すると、標準SQLと同じく、名前を囲む場合に「"」を使います。
どちらのモードでも、「`」（バッククォート）で囲んだものは、名前として扱われます。

お礼

ご回答、ありがとうございました。

No1さんも、検索結果が出るところまでは助かりましたが、table B のフィールドに値が無いか、登録そのものが無いデータはひっからないという問題が起こり、困っていたのですが、このSQLでうまくいきました。

Access　では、単に　&　でつなげるだけでＯＫだったのですが、MySQLはちょっと複雑ですね。
困っていたので助かりました。

ありがとうございます。

回答No.3

yambejp

Bのoption1とoption2からAのidを抽出するんですよね？
であればこんなかんじ・・・
（無駄な文字結合などいりません）

LでRedな場合・・・
select id from A
inner join B on A.No=B.No
AND option1='L' and option2='Red'

Lで値なしの場合・・・
select id from A
inner join B on A.No=B.No
AND option1='L' and option2 is null

Bテーブルを作るときにNULLの処理をきちんと定義していないとはまります

お礼

ありがとうございます。

残念な事に、NOとoption1、option2　に分かれていたデータは、結合されて相手に渡り、戻ってくるときは　01LRed　や、11S　や20　と、ユニークに分割出来ない形で返ってくるため、フィールドを結合して検索するという手段をとらざるをえませんでした……

Accessでは簡単に検索できていたのですが……

ありがとうございました。

回答No.1

moousi

複数のテーブルを結合して、検索することにはまったく問題がありません。

文字列結合の仕方は、Accessと異なります。
文中でもありますように concatを使います。

concat(カラム名1, カラム名2, カラム名3, ・・・・)
concat('文字1', '文字2', '文字3', ・・・・)
concat(カラム名, '文字1', ・・・・）

使いどころにもよりますが、上記のconcatは有効なものです。

さて、SQLですが、

SELECT A.id, concat(A.No, B.option1, B.option2) AS test FROM A
LEFT JOIN B ON A.No = B.No
WHERE concat(A.no, B.option1, B.option2) = '01LRED';

これで検索できます。

ご存知かとは思いますが、WHEREの左辺で算術演算子や関数を使わないようにする方が
indexを使われる可能性がありますので有利です。

こちらでも同様の結果が求められます。
WHERE a.no='01' AND b.option1='L' AND b.option2='RED';

お礼

ありがとうございます。

01LRED　は確かに検索できたので、これでＯＫか！　……と思ったのですが、20　や　11S　では

Empty set (0.00 sec)

が返って、うまくいきませんでした。

でもかなり助かりました。ありがとうございました。