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.

Fiesole

Answer 1

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.

Shirlshirlee

and if you want to use the count(myTable.*) how does that work? – Humanoid

you can also use aliases, so when you go select * from tablename as tn, you could write select tn.* from tablename as tn. – Chesterfieldian

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! – Stormproof

Nah, that creates tons of "duplicate" rows – Hamburg

Answer 2

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)

Laughingstock

Answer 3

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.

Almoner

Answer 4

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

Malm

Good answer, helpful indeed! – Transept