使用联接和where子句

Question

SELECT * FROM Base b 
JOIN Departement d ON b.d_id = d.d_id 
JOIN Block bl ON b.b_id = bl.b_id 
JOIN Model m ON b.m_id = m.m_id 
WHERE (b.s_no LIKE '%' + @search +'%' 
OR b.ser_no LIKE '%' + @search + '%'
OR b.user_name LIKE '%' + @search + '%' 
OR d.d_id LIKE '%' + @search + '%' 
OR bl.b_id LIKE '%' + @search + '%' 
OR m.m_id LIKE '%' + @search + '%' 
OR d.d_name LIKE '%' + @search + '%' 
OR m.m_name LIKE '%' + @search + '%') ";

我试图编写一个sql命令来搜索数据库。但不起作用。这是假用法吗？

Answer 1

也许你需要外部连接而不是内部连接。不确定您的情况有什么问题，但是尝试使用左联接。也许您只是在一个表中有数据，但在另一个表中没有相应的数据。

而不是

 SELECT * FROM Base b 
 JOIN Departement d ON b.d_id = d.d_id 
 JOIN Block bl ON b.b_id = bl.b_id 
 JOIN Model m ON b.m_id = m.m_id 
 WHERE (b.s_no LIKE '%' + @search +'%' 
 OR b.ser_no LIKE '%' + @search + '%'
 OR b.user_name LIKE '%' + @search + '%' 
 OR d.d_id LIKE '%' + @search + '%' 
 OR bl.b_id LIKE '%' + @search + '%' 
 OR m.m_id LIKE '%' + @search + '%' 
 OR d.d_name LIKE '%' + @search + '%' 
 OR m.m_name LIKE '%' + @search + '%') ";

试试这个：

 SELECT * FROM Base b 
 LEFT JOIN Departement d ON b.d_id = d.d_id 
 LEFT JOIN Block bl ON b.b_id = bl.b_id 
 LEFT JOIN Model m ON b.m_id = m.m_id 
 WHERE (b.s_no LIKE '%' + @search +'%' 
 OR b.ser_no LIKE '%' + @search + '%'
 OR b.user_name LIKE '%' + @search + '%' 
 OR d.d_id LIKE '%' + @search + '%' 
 OR bl.b_id LIKE '%' + @search + '%' 
 OR m.m_id LIKE '%' + @search + '%' 
 OR d.d_name LIKE '%' + @search + '%' 
 OR m.m_name LIKE '%' + @search + '%') ";

如果你告诉我们细节上出了什么问题--我们就能给出更详细的假设:)