For A multi-table query, the 10 fields in SQL statement A are aid, title, cid, content, atime, aorder, acount, a1, a2, in the mtimeB table, the nine fields are bid, title, cid, content, btime, border, b3, b4, and mtime. now I obtain an SQL statement for multi-table queries.
10 fields in Table
The fields are aid, title, cid, content, atime, aorder, acount, a1, a2, and mtime.
9 fields in Table B
The fields are bid, title, cid, content, btime, border, b3, b4, and mtime.
Now I get A keyword $ kw. I need to search for the title fields of Table A and Table B at the same time, get all records of like % $ kw %, and sort them by mtime.
The fields to be retained in the obtained records include a (B) id, title, cid, content, and 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';
Is it feasible to construct an SQL statement like this?
If not, how can I write this SQL statement?
------ Solution --------------------
Let's take a look at union.
Two select statements use union in the middle, and the fields after the select statement must be the same.
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 %'
------ Solution --------------------
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