php如何使用Condition获取多个表列值

Question

First, thanks for your guy spend time to read this.

Now I have two table, and the data is below:

table name: table1

+-----------+----------+---------------------------+
| tid       | area     | Subject                   |
+-----------+----------+---------------------------+
| 1         | US       | The one restaurant        |
| 2         | US       | Landmark hotel            |
| 3         | US       | Tholo restaurant          |
| 4         | CA       | GE bar                    |
+-----------+----------+---------------------------+

table name: table2

+--------+---------+---------------------------+---------------+
| tid    | area    | Value                     | optionid      |
+--------+---------+---------------------------+---------------+
| 1      | US      | the one restaurant desc   | restaurant    |
| 1      | US      | the one rest. contact     | recontact     |
| 1      | US      | the one rest. address     | readdress     |
| 2      | US      | landmark hotel desc       | hotel         |
| 2      | US      | landmark hotel.contact    | hocontact     |
| 2      | US      | landmark hotel.address    | hoaddress     |
| 3      | US      | Tholo restaurant.desc     | restaurant    |
| 3      | US      | Tholo restaurant.contact  | recontact     |
| 3      | US      | Tholo restaurant.address  | readdress     |
| 4      | CA      | GE bar.desc               | bar           |
| 4      | CA      | GE bar.contact            | bacontact     |
| 4      | CA      | GE bar.address            | baaddress     |
+--------+---------+---------------------------+---------------+

I would like to show the data like below when user query area=US

|tid  | Subject              | description             | contact                     |    Address               |area|
+-----+----------------------+-------------------------+-----------------------------+--------------------------+----+
| 1   | The one restaurant   | the one restaurant desc | the one rest.contact        | the one rest.address     | US |
| 2   | Landmark hotel       | landmark hotel desc     | landmark hotel contact      | landmark.address         | US |
| 3   | Tholo restaurant     | Tholo restaurant.desc   | Tholo restaurant.contact    | Thoro restaurant.address | US |

Now I only can success alias and union, can't join the subject, and my code is below:

$query = mysql_query("SELECT value AS restaurant_description FROM table2 WHERE fid = US AND optionid = restaurant UNION SELECT value AS restaurant_contact FROM table2 WHERE fid = US AND optionid = recontact UNION SELECT value AS restaurant_address FROM table2 WHERE fid = US AND optionid = readdress UNION SELECT value AS hotel FROM table2 WHERE fid = US AND optionid = hotel UNION SELECT value AS hotel_description FROM table2 WHERE fid = US AND optionid = hotel UNION SELECT value AS hotel_contact FROM table2 WHERE fid = US AND optionid = hocontact UNION SELECT value AS hotel_address FROM table2 WHERE fid = US AND optionid = hoaddress UNION SELECT value AS bar_description FROM table2 WHERE fid = US AND optionid = bar UNION SELECT value AS bar_contact FROM table2 WHERE fid = US AND optionid = bacontact UNION SELECT value AS bar_address FROM table2 WHERE fid = US AND optionid = baaddress"); 

    while($row = mysql_fetch_array($query)) {
        echo $row['hotel_description'];
        echo $row['hotel_lat'];
        echo $row['subject'];
}

I have try the code below, but it cant show the data i need.

$query = mysql_query("SELECT value AS restaurant_description FROM table2 WHERE fid = US AND optionid = restaurant UNION SELECT value AS restaurant_contact FROM table2 WHERE fid = US AND optionid = recontact UNION SELECT value AS restaurant_address FROM table2 WHERE fid = US AND optionid = readdress UNION SELECT value AS hotel FROM table2 WHERE fid = US AND optionid = hotel UNION SELECT value AS hotel_description FROM table2 WHERE fid = US AND optionid = hotel UNION SELECT value AS hotel_contact FROM table2 WHERE fid = US AND optionid = hocontact UNION SELECT value AS hotel_address FROM table2 WHERE fid = US AND optionid = hoaddress UNION SELECT value AS bar_description FROM table2 WHERE fid = US AND optionid = bar UNION SELECT value AS bar_contact FROM table2 WHERE fid = US AND optionid = bacontact UNION SELECT value AS bar_address FROM table2 WHERE fid = US AND optionid = baaddress UNION SELECT subject FROM table1 WHERE fid = US AND table1.tid = table2.tid"); 

Answer 1

Thats more like creating pivot table. If you only want recontact ,restaurant and readdress values for each you can use the following technique.

select
t1.tid,
t1.subject,
t2.area,
max(case when t2.optionid = 'restaurant' then t2.Value end) as `description`,
max(case when t2.optionid = 'recontact' then t2.Value end ) as `contact`,
max(case when t2.optionid = 'readdress' then t2.Value end ) as `Address`
from table1 t1
join table2 t2 on t1.tid = t2.tid
group by t1.tid;