求一個多表查詢的sql語句
A表10個欄位
欄位分別為aid, title, cid, content, atime, aorder, acount，a1, a2, mtime
B表9個欄位
欄位分別為bid, title, cid, content, btime, border, b3, b4, mtime
現在我擷取到一個關鍵詞$kw,我需要同時搜尋A表和B表的title欄位，擷取到所有like %$kw%的所有記錄,並根據mtime來排序。
擷取的記錄裡，需要保留的欄位有a(b)id,title,cid,content,mtime這幾個。
SQL code
$sql = 'SELECT ';$sql .= 'A.aid as id,B.bid AS id,';$sql .= 'A.title as title,B.title as title,';$sql .= 'A.cid as cid,B.cid as cid,';$sql .= 'A.content as content,B.content as content,';$sql .= 'A.mtime as mtime,B.mtime as mtime';$sql .= 'FROM A,B WHERE title like "%'.$kw.'%" ORDER BY mtime DESC';
我這樣構造sql,可行嗎？
如果不可行，那麼怎麼寫這個sql語句呢？
------解決方案--------------------
去看一下 union
兩個 select 然後中間用一下 union , 要求 select 後面的欄位都一樣的
select a.id, a.xxx from a where a.title like '%$km%'
union
select b.id, b.xxx from b where b.title like '%$km%'
------解決方案--------------------
select aid, title, cid, content,mtime from a where title like '%km%'
order by mtime desc
union all
select bid,title,cid,content,mtime from b where title like '%km%'
order by mtime desc
