在多个表中搜索单个值 [英] Search for a single value in multiple tables

问题描述

我正在使用VB.net在ASP中开发一个应用程序。

在这个应用程序中，一个用户只能申请四分之一的应用程序。这些应用程序的记录放在四个表中，比如t1，t2，t3，t4。



我想搜索用户是否已申请任何申请？



所有四个表都有不同的列名，但userID（记录）将是相同的。



Pl帮助



我尝试过：

I am developing an application in ASP with VB.net.
In this application one user can apply for only one out of four applications. Records of these applications are placed in four tables say t1 , t2, t3, t4.

I want to search if user has already applied for any of the application or not?

All four tables are having different column Names but userID (record) will be same.

Pl help

What I have tried:

Sql = "Select FA.ApplicationID, TFA.ApplicationID, AC.ApplicationID " &
      "FROM (FreshApplications as FA  " &
      "INNER JOIN TempFreshApplications as TFA on U.UPCNo = TFA.UserID) " &
      "INNER JOIN ApplicationsForChange as AC on U.UPCNo = AC.UPCNo" &
      " where U.UPC = '" & Session("LoggedInUPCNo") & "'"

推荐答案

尝试使用Union All声明



参见< br $> b $ b

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql [ ^ ]



所以你可以做类似的事情

Try using Union All statement

See

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql[^]

So you can do something like

sql = "Select sum(id) as Num from
(
Select count(userid) as id from t1 where userid = " & Session("LoggedInUPCNo") & "
union all
Select count(userid) as id from t2 where userid = " & Session("LoggedInUPCNo") & "
union all
Select count(userid) as id from t3 where userid = " & Session("LoggedInUPCNo") & "
union all
Select count(userid) as id from t4 where userid = " & Session("LoggedInUPCNo") & ") t"

警告：一个好的做法是你应该总是使用参数而不是字符串连接，无论查询是什么私下或公开生成的。有一天，查询可能会公开，然后 SQL注入攻击 [ ^ ]会危及数据的安全性。

Warning: A good practice is that you should always use parameters and not string concatenation regardless if the query is privately or publicly generated. One day, the query may be made public then it will be open to SQL Injection attacks[^] which will compromise the security of the data.

这篇关于在多个表中搜索单个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！