Search code examples
mysqlrdbms

Is there any way to know which table contain the specific entry while searching multiple tables in MySql

I have multiple tables in a database. I'm searching all the tables for a specific entry. The search result returns correctly. But how do i know which table contains that specific data/entry in MySql? Is there any way to return/know the table name that contains the specific entry while searching multiple tables?

Dim statusQuery As String = $"SELECT * FROM management.hostelA,management.hostelB,management.hostelC,management.hostelD where hostelA.occupant1='{stu_name.Text}' or hostelA.occupant2='{stu_name.Text}' or hostelB.occupant1='{stu_name.Text}' or hostelB.occupant2='{stu_name.Text}' or hostelC.occupant1='{stu_name.Text}' or hostelC.occupant2='{stu_name.Text}' or hostelD.occupant1='{stu_name.Text}' or hostelD.occupant2='{stu_name.Text}'"
cmd = New MySqlCommand(statusQuery, con)
Dim sdr As MySqlDataReader
sdr = cmd.ExecuteReader
While sdr.Read
   statusflag = statusflag + 1
End While

I want to know which table contains the stu_name.text

Solution

  • You're better off querying each table individually, i.e. one table at a time. Then you will easily see which table the name was found in.

    Despite the comment from @Uueerdo, you're not doing a bunch of UNION operations, you're doing a bunch of JOIN operations. This means your query is doing a huge Cartesian product of four tables. In other words, every row of all four tables is matched to every row of all other tables. I'm sure this is not what you intended.

    You need to learn SQL before you try to do things like this.

    Until then, you should query the tables one at a time.