Skip to content

Search between two SQL tables?

I have two tables:

student

Columns

first_name
last_name

teacher_accounts

Columns

fname
lname

I have an input form that takes the data entered into an input field, and passes it through the following SQL query:

select * from student WHERE first_name like '%$q%' OR last_name like '%$q%'

However, I want to now join the teacher_accounts table to see if the name entered possibly matches what is entered into the input field. Essentially I need an SQL statement that checks the teacher_accounts and student table against the data entered into the input field.

Thanks for any help!

UPDATE

I need to mention that I’m outputting this data using mysql_fetch_array, so I need a way for the script to discern whether or not it needs to pull from the teacher columns or the student columns.

    while($row=mysql_fetch_array($result)) {
echo "<a href='?fname=".$row['first_name']."&lname=".$row['last_name']."'>".$row['first_name']." ".$row['last_name']."<br />";

Answer

SELECT first_name, last_name, 'student' as `type`
FROM   students
WHERE  first_name like '%<your string>%'
   OR  last_name  like '%<your string>%'

UNION

SELECT fname, lname, 'teacher' as `type`
FROM   teacher_accounts
WHERE  fname like '%<your string>%'
   OR  lname like '%<your string>%'

Refer to: http://dev.mysql.com/doc/refman/5.6/en/sql-syntax-prepared-statements.html for how to prepare statements and pass values safely using placeholders.