Joining tables in MySQL and requesting data from second table only

Question

I'm trying to join 2 tables where I need to show only 3 columns from the second one where another column is used as a comparison.

For example:

Table one is called employee: it has a column called user_id and some other columns

Table two is called people: it has a column called user_id which included some of the employees user_ids

The columns I want to select are all from table people! (firstname, lastname, email)

I tried the following but something going wrong:

SELECT userid, firstname, lastname, email 
FROM people 
JOIN employee 
WHERE people.userid = employee.userid;

I'm not sure what am I doing wrong, could you please help me correct it?

Answer 1

You can try this query:

SELECT 
     p.userid, 
     p.firstname, 
     p.lastname, 
     p.email 
FROM 
     people as p, 
     employee as emp 
WHERE 
     p.userid = emp.userid

Answer 2

Looking at your script, it looks like you'll run into ambiguous columns in at least your userid. You want to explicitly tell SQL where the column comes from like in your WHERE clause if there are columns sharing the same name between the two tables.

SELECT
  userid, -- AMBIGUOUS 
  firstname,
  lastname, 
  email 
  FROM people
    JOIN employee 
  WHERE people.userid = employee.userid;

Example solution:

SELECT
  people.userid,
  people.firstname,
  people.lastname,
  people.email
  FROM people
    JOIN employee 
  WHERE people.userid = employee.userid;

Answer 3

For this issue you can use this query

let suppose that I have a users table where a user have zero to one profile picture

I need the user (Name,LastName,BirthDate) for users who have no profile picture I can use this query

select * 
from user c
where  NOT EXISTS (
    select 1 
    from photo p
    where p.id = c.photo_id
)

in this where you can use any field between this two table

removing the not will result on the users who have a profile picture

hope this help you

you can search for SEMI JOIN and ANTI JOIN for more informations