CODEWITHSUNDEEP
mysql
Sam Edward
Sam Edward

Reputation: 309

Joining tables in MySQL and requesting data from second table only

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?

Upvotes: 5

Views: 87

Answers (4)

DirWolf
DirWolf

Reputation: 891

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

Upvotes: 5

Riss
Riss

Reputation: 3

i think this query will solve your problem

insert into table1 (clmn_1,clmn_2,clmn_3) SELECT clmn_1,clmn_2,clmn_3 FROM table2 where id = value

Upvotes: 0

Mohammed Housseyn Taleb
Mohammed Housseyn Taleb

Reputation: 1828

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

Upvotes: 2

Dan
Dan

Reputation: 2725

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;

Upvotes: 2

Related Questions