添加符号连接多个表

w6lpcovy 于 2021-06-15 发布在 Mysql

关注(0)|答案(1)|浏览(258)

我有两个查询从多个表中获取行。这些查询已经过测试并返回我想要的行。
这里是第一个问题。

select mst_province.provinsi as provinsi, mst_kab.kabupaten as kabupaten, form_kuesioner_pengelola.namasite as nama 
from form_kuesioner_pengelola 
join form_kuesioner_surveyor on form_kuesioner_surveyor.namalokasi = form_kuesioner_pengelola.namasite 
join mst_province on mst_province.id_prov = form_kuesioner_pengelola.idprov 
join mst_kab on mst_kab.id_prov = form_kuesioner_pengelola.idprov and mst_kab.id_kab = form_kuesioner_pengelola.idkab 
group by form_kuesioner_pengelola.namasite 
order by form_kuesioner_pengelola.idprov, form_kuesioner_pengelola.idkab

我把它们放在加了符号的表格里

echo "<tr> <th>No.</th> <th>Provinsi</th> <th>Kabupaten</th> <th>Namasite</th> <th>Form Manfaat</th> <th>Form Pengelola</th> <th>Form Surveyor</th> <th>status</th>";

while($row = mysqli_fetch_array($rdata)) {
    echo "<tr>";
    echo '<td>' . $no . '</td>';
    echo '<td>' . $row['provinsi'] . '</td>';
    echo '<td>' . $row['kabupaten'] . '</td>';
    echo '<td>' . $row['nama'] . '</td>';
    echo '<td>&#9989;</td>';
    echo '<td>&#9989;</td>';
    echo '<td>&#9989;</td>';
    echo '<td>complete</td>';

    echo "</tr>";
    $no++;
}

第二个

select mst_province.provinsi as provinsi, mst_kab.kabupaten as kabupaten, form_kuesioner_pengelola.namasite as nama 
from form_kuesioner_pengelola 
join mst_province on mst_province.id_prov = form_kuesioner_pengelola.idprov 
join mst_kab on mst_kab.id_prov = form_kuesioner_pengelola.idprov and mst_kab.id_kab = form_kuesioner_pengelola.idkab 
group by form_kuesioner_pengelola.namasite 
order by form_kuesioner_pengelola.idprov, form_kuesioner_pengelola.idkab

我把它们放在下面的表格里

echo "<tr> <th>No.</th> <th>Provinsi</th> <th>Kabupaten</th> <th>Namasite</th> <th>Form Manfaat</th> <th>Form Pengelola</th> <th>Form Surveyor</th> <th>status</th>";

while($rowtable = mysqli_fetch_array($rdatatable)) {
    echo "<tr>";
    echo '<td>' . $notable . '</td>';
    echo '<td>' . $rowtable['provinsi'] . '</td>';
    echo '<td>' . $rowtable['kabupaten'] . '</td>';
    echo '<td>' . $rowtable['nama'] . '</td>';  
    echo '<td>&#9989;</td>';
    echo '<td>&#10006;</td>';
    echo '<td>&#10006;</td>';

    echo '<td>visited</td>';

    echo "</tr>";
    $notable++;
}

问题是他们用自己的表打印，这样会返回2个表。如何将它们连接在一起，这样我就有了一个基于它们自己表的带有不同符号的表。
谢谢

sql mysql php

来源：https://stackoverflow.com/questions/54123742/join-multiple-table-with-symbol-added

1条答案

按热度按时间

soat7uwm1#

可以使用组合这两个查询 UNION .

SELECT *
FROM (
    select 'complete' as which, mst_province.provinsi as provinsi, mst_kab.kabupaten as kabupaten, form_kuesioner_pengelola.namasite as nama, form_kuesioner_pengelola.idprov, form_kuesioner_pengelola.idkab
    from form_kuesioner_pengelola 
    join form_kuesioner_surveyor on form_kuesioner_surveyor.namalokasi = form_kuesioner_pengelola.namasite 
    join mst_province on mst_province.id_prov = form_kuesioner_pengelola.idprov 
    join mst_kab on mst_kab.id_prov = form_kuesioner_pengelola.idprov and mst_kab.id_kab = form_kuesioner_pengelola.idkab 
    group by form_kuesioner_pengelola.namasite 

    UNION ALL

    select 'visited' as which, mst_province.provinsi as provinsi, mst_kab.kabupaten as kabupaten, form_kuesioner_pengelola.namasite as nama, form_kuesioner_pengelola.idprov, form_kuesioner_pengelola.idkab
    from form_kuesioner_pengelola 
    join mst_province on mst_province.id_prov = form_kuesioner_pengelola.idprov 
    join mst_kab on mst_kab.id_prov = form_kuesioner_pengelola.idprov and mst_kab.id_kab = form_kuesioner_pengelola.idkab 
    group by form_kuesioner_pengelola.namasite 
) AS u
order by u.idprov, u.idkab

那你可以用 $row['which'] 知道是否 ✅ 或者 ✖ 在最后两列中。

echo "<tr> <th>No.</th> <th>Provinsi</th> <th>Kabupaten</th> <th>Namasite</th> <th>Form Manfaat</th> <th>Form Pengelola</th> <th>Form Surveyor</th> <th>status</th>";

while($row = mysqli_fetch_array($rdata)) {
    echo "<tr>";
    echo '<td>' . $no . '</td>';
    echo '<td>' . $row['provinsi'] . '</td>';
    echo '<td>' . $row['kabupaten'] . '</td>';
    echo '<td>' . $row['nama'] . '</td>';
    echo '<td>&#9989;</td>';
    echo '<td>' . ($row['which'] == 'complete' ? '&#9989;' : '&#10006;') . '</td>';
    echo '<td>' . ($row['which'] == 'complete' ? '&#9989;' : '&#10006;') . '</td>';
    echo '<td>' . $row['which'] . '</td>';

    echo "</tr>";
    $no++;
}

如果你只想把所有东西都归还一次 Complete 或者 Visited 取决于他们是否在 form_kuesioner_surveyor table，你应该用 LEFT JOIN 用那张table。然后可以测试表中的列是否为null，以了解是否存在匹配项。

select IF(form_kuesioner_surveyor.namalokasi IS NULL), 'visited', 'complete') as which, mst_province.provinsi as provinsi, mst_kab.kabupaten as kabupaten, form_kuesioner_pengelola.namasite as nama
from form_kuesioner_pengelola 
join mst_province on mst_province.id_prov = form_kuesioner_pengelola.idprov 
join mst_kab on mst_kab.id_prov = form_kuesioner_pengelola.idprov and mst_kab.id_kab = form_kuesioner_pengelola.idkab 
left join form_kuesioner_surveyor on form_kuesioner_surveyor.namalokasi = form_kuesioner_pengelola.namasite     
group by form_kuesioner_pengelola.namasite 
order by form_kuesioner_pengelola.idprov, form_kuesioner_pengelola.idkab

赞(0）回复(0）举报 2021-06-15

我来回答

添加符号连接多个表

1条答案

相关问题

热门标签

最新问答