MySQL Select all columns from one table and some from another table

Question

How do you select all the columns from one table and just some columns from another table using JOIN? In MySQL.

score 649 · Accepted Answer · answered Aug 16 '10 at 12:06

649

Just use the table name:

SELECT myTable.*, otherTable.foo, otherTable.bar...

That would select all columns from myTable and columns foo and bar from otherTable.

answered Aug 16 '10 at 12:06

Tatu Ulmanen

and if you want to use the count(myTable.*) how does that work? – Stevanicus Apr 26 '12 at 16:41
you can also use aliases, so when you go select * from tablename as tn, you could write select tn.* from tablename as tn. – adudley Apr 03 '15 at 12:24
My MySql DB dont need to prefix fields from other tables. Ex: SELECT table1.*, field1InTable2, field1InTable3, field2InTable3 from table1 join table2 on .... join table3 on ..., works! – gillall Oct 12 '21 at 20:13
Nah, that creates tons of "duplicate" rows – sba May 23 '23 at 13:29

score 55 · Answer 2 · answered Aug 16 '10 at 12:08

55

I need more information really but it will be along the lines of..

SELECT table1.*, table2.col1, table2.col3 FROM table1 JOIN table2 USING(id)

answered Aug 16 '10 at 12:08

Simon

score 31 · Answer 3 · edited Jun 01 '19 at 07:49

31

select a.* , b.Aa , b.Ab, b.Ac from table1 a left join table2 b on a.id=b.id

this should select all columns from table 1 and only the listed columns from table 2 joined by id.

edited Jun 01 '19 at 07:49

Pavindu

answered Jul 17 '17 at 15:15

Mzila

score 5 · Answer 4 · answered Mar 13 '18 at 14:34

5

Using alias for referencing the tables to get the columns from different tables after joining them.

Select tb1.*, tb2.col1, tb2.col2 from table1 tb1 JOIN table2 tb2 on tb1.Id = tb2.Id

answered Mar 13 '18 at 14:34

Himanshu

4 Answers4