Joining tables in MySQL and querying data from only the second table

Question

I am trying to join two tables where I need to show only 3 columns from the second, where another column is used as a comparison.

For instance:

The first table is called employee: it has a user_id column and some other columns

The second table is called people: it has a column user_id, which includes some of the users user_ids

The columns that I want to select are all from the people in the table! (first name, last name, email address)

I tried the following, but something is wrong:

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

I'm not sure what I'm doing wrong, could you help me fix this?

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 will encounter ambiguous columns, at least with your user id. You want to explicitly specify SQL where the column occurs, as in your WHERE clause, if there is 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 problem you can use this query

suppose I have a user table in which the user has an image with a value of zero to one profile

I need a user (Name, LastName, BirthDate) for users who do not have a profile. 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 place where you can use any field between these two tables

deleting this will not result in users who have a profile picture

hope this helps you

You can find SEMI JOIN and ANTI JOIN for more information.

Answer 4

I think this request 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