CODEWITHSUNDEEP
mysqljoinalias
Benjamin Brownlee
Benjamin Brownlee

Reputation: 444

MySQL Joining Mess

In my developing messaging application, I have a table of users with basic account information (firstname, lastname, username, encrypted password, etc.) and table of contacts with two columns (foreign keys to the previous table) that show one user has another in his contacts. Both tables have a standard auto-increment id column as a primary key.

I have a search bar that allows users to find others by searching keywords so that they could bookmark them in their contacts. I could fetch this data using this type of query, with $word being the search word and $id being the logged in user's id from the session table:

select firstname
     , lastname
     , username
     , id 
  from users 
 where (locate('$word', username) > 0 
     or locate('$word', firstname) > 0 
     or locate('$word', lastname) > 0) 
   and id <> '$id' 
 order 
    by username desc 
 limit 20

But I found it was necessary for the user to know if the users he was searching were already in their contacts or not, so I need to join this with contact information. I tried this query:

select users.firstname, 
       users.lastname, 
       users.username, 
       users.id, 
       contactsPrime.id as contact from users 
left join 
       (select * from contacts as contactsPrime 
       where contacts.userID = '$id') 
on users.id = contactsPrime.contactID 
       and (locate('$word', users.username) > 0 
          or locate('$word', users.firstname) > 0 
          or locate('$word', users.lastname) > 0) 
order by username desc limit 20

I used the alias table contactsPrime, but I'm still getting errors that all my derived tables don't have an alias name. Where did I go wrong? Is there a better way to be achieving the effect I want?

Upvotes: 0

Views: 37

Answers (1)

Ravi
Ravi

Reputation: 31417

It's about scoping, the scope of contactsPrime within () and can't be accessible outside.

   (select * from contacts as contactsPrime 
   where contacts.userID = '$id')

You will get error at below line

on users.id = contactsPrime.contactID

You need to change it to

(select * from contacts where contacts.userID = '$id') as contactsPrime

Upvotes: 1

Related Questions