CODEWITHSUNDEEP
mysqlsql
Adham
Adham

Reputation: 64844

What is the error in this SQL command?

What is the error in this SQL command

select account_id,fname,lname ,
    (select email from account where id=account_id) as email 
from user_account 
where account_id in (
       select destination_id 
       from friendship 
       where source_id=$user_id) 
    and (concat(fname,lname) like '%a%' or email like '%a%')

Where email is a column in account table

The error message is : Unknown column email

I want to get email and test it same as fname+lname

Upvotes: 1

Views: 87

Answers (4)

Jonathan Leffler
Jonathan Leffler

Reputation: 753695

Reformatting your query for legibility:

SELECT account_id, fname, lname,
       (SELECT email FROM account WHERE id = account_id) AS email
  FROM user_account 
 WHERE account_id IN (SELECT destination_id FROM friendship WHERE source_id = $user_id)
   AND (CONCAT(fname, lname) LIKE '%a%' OR email LIKE '%a%')

The most probable cause of trouble is that the only table that the column email can refer to in the main WHERE clause is user_account, but the email column is only found in the account table.

The simplest fix is to remove the sub-query in the select-list and use a regular join instead:

SELECT u.account_id, u.fname, u.lname, a.email
  FROM user_account AS u
  JOIN account      AS a ON a.id = u.account_id
 WHERE account_id IN (SELECT destination_id
                        FROM friendship AS f
                       WHERE f.source_id = $user_id)
   AND (CONCAT(u.fname, u.lname) LIKE '%a%' OR a.email LIKE '%a%');

I'm assuming that $user_id is an input value and the notation makes sense in your system; it is not generally valid in SQL.

It is not clear that there is much benefit to the IN clause; that too could be made into a join with the other tables.

Upvotes: 1

p.campbell
p.campbell

Reputation: 100567

Sounds like your table user_account doesn't have a column named email referenced in your WHERE clause. Try this with a JOIN instead:

   select ua.account_id,
          ua.fname,
          ua.lname, 
          a.email 
from user_account ua
inner join  account a where ua.id = a.account_id
and a.account_id  in ( select destination_id from friendship where source_id=$user_id) 
and (concat(fname,lname) like '%a%' or email like '%a%');

Upvotes: 2

Senthil
Senthil

Reputation: 106

I assue that your id in Accout table is same as Account_id in User_Account and also the Destination_id in friendship tables. 

select ua.account_id,
       ua.fname,
       ua.lname, 
       a.email 
from user_account ua
inner join  account a where ua.id = a.account_id
and a.account_id  
in ( select destination_id from friendship where source_id=$user_id) and (concat(fname,lname) like '%a%' or email like '%a%')

Upvotes: 0

Sparky
Sparky

Reputation: 15075

What is the error in this SQL command

select account_id,fname,lname ,
(select email from account where id=account_id) as email from user_account 
where account_id 
  in ( select destination_id from friendship where source_id=$user_id) and          (concat(fname,lname) like '%a%' or email like '%a%')

The problem is that, in the following syntax, email is a table alias, not a column name

(select email from account where id=account_id) as email

It looks like you've already got a better approach from p.campell though

Upvotes: 0

Related Questions