Search code examples
mysqlinner-joinalias

Using a column alias in an INNER JOIN query?

My query:

SELECT Name, A, B, C, D, E, F 
FROM Data 
INNER JOIN User_Access ON Data.Name = User_Access.Name 
WHERE User_Access.Channel = 'TEST'

I have to use an alias for "Name" but I cannot.

My best almost-answer:

SELECT Name AS N, A, B, C, D, E, F 
FROM Data 
INNER JOIN User_Access ON Data.N = User_Access.Name 
WHERE User_Access.Channel = 'TEST'

But it's not working and I have an invalid name error.

How do I use a column alias in an INNER JOIN query?

Solution

  • Where a column name is common between n tables you need to qualify it so that sql knows which one you mean.This will work

    SELECT data.Name, A, B, C, D, E, F 
FROM Data 
    INNER JOIN User_Access ON Data.Name = User_Access.Name 
WHERE User_Access.Channel = 'TEST'

    as will 

    SELECT d.Name, A, B, C, D, E, F 
FROM Data d
    INNER JOIN User_Access ON d.Name = User_Access.Name 
WHERE User_Access.Channel = 'TEST'