CODEWITHSUNDEEP
mysqlsql
pauloz1890
pauloz1890

Reputation: 800

Joining and querying two tables with conflicting column names

Imagine I have two tables: users and students

Each table has an id and email column. I want to be able to search for emails from both tables. I can join the two tables where the IDs match like this:

SELECT users.email,students.email as student_email FROM users
INNER JOIN students ON users.id = students.id

I can't search the email column because they have the same column name (email). So if I try use WHERE it complains that the column (email) is ambiguous. If I try use student_email it complains that the column doesn't exist.

If the two tables didn't use an identical column name (email) then it would be fine when I use WHERE.

The only solution I can think of is to get all rows and then loop around them and search the email strings manually. This is extremely slow compared to using MySQL like this:

SELECT users.email,students.email as student_email FROM users
INNER JOIN students ON users.id = students.id
WHERE email LIKE '%[email protected]%' OR student_email LIKE '%[email protected]%'

But of course this doesn't work because WHERE looks for the original column names before the SELECT.

How can I get around this without getting all the rows and the looping it? Maybe it is a union, rather than a JOIN? I'm not sure...

Upvotes: 0

Views: 1942

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Just use the qualified column name. I also recommend table aliases:

SELECT u.email, s.email as student_email
FROM users u INNER JOIN
     students s
     ON u.id = s.id
WHERE u.email LIKE '%[email protected]%' OR
      s.email LIKE '%[email protected]%';

Whenever you have more than one table in a query, you should always qualify all the column names. Using abbreviations for the tables as table aliases makes the query easier to write and to read.

Upvotes: 5

Related Questions