Join two table

[luzz]

Hi all, may i know how to perform join on two table?


I tried using join but it is not the result that i want.
Below is my SQL Code:
SELECT *

FROM Table1 INNER JOIN Table2 ON Table1.PO = Table2.PO;

I want the table to show all the fields in both table 1 and table 2

 

[isladogs]

Try this SQL Code:

SELECT Table1.*, Table2.* FROM Table1 INNER JOIN Table2 ON Table1.PO = Table2.PO;

 

[luzz]

Try this SQL Code:

SELECT Table1.*, Table.* FROM Table1 INNER JOIN Table2 ON Table1.PO = Table2.PO;

Hi, it did join all my fields together but it show duplicate column. How can i get rid of it?

 

[isladogs]

Hi, it did join all my fields together but it show duplicate column. How can i get rid of it?

Yes it will do as you have PO in both tables.
So the easiest solution is use .* for Table1 then enter all the fields individually from Table2 except PO ... OR do the same the other way round

 

[CJ_London]

Not sure why your first query didn't work - it should show all fields as does Colins

you can just ignore one or the other PO column in your form or report

 

[isladogs]

Not sure why your first query didn't work - it should show all fields as does Colins

you can just ignore one or the other PO column in your form or report

Hi CJ
The original code by Luzz was

SELECT * FROM Table1 INNER JOIN Table2 ON Table1.PO = Table2.PO;

That will of course only show the fields from Table1

 

[CJ_London]

SELECT * FROM Table1 INNER JOIN Table2 ON Table1.PO = Table2.PO;

That will of course only show the fields from Table1

you sure about that? On what basis?

 

[isladogs]

you sure about that? On what basis?

I just tried it to make absolutely sure of my facts !!!
I even tried it for Select Table2.* separately to Table1.*

Perhaps you have an example that brings in fields from both tables without listing any from the second table.
If so, I'd like to see it as it would potentially save lots of work in future!!!!

 

[CJ_London]

OP said

I want the table to show all the fields in both table 1 and table 2

SELECT * FROM will do that

Perhaps you have an example that brings in fields from both tables without listing any from the second table.

both tables without listing from second table? don't understand

 

[isladogs]

Hi CJ

Sorry - we were at cross purposes.

You are quite right in saying that:

SELECT * FROM Table1 INNER JOIN Table2 ON Table1.PO = Table2.PO;

gives exactly the same results as

SELECT Table1.*, Table2.* FROM Table1 INNER JOIN Table2 ON Table1.PO = Table2.PO;

As the OP had included Table1.*, I added to that whereas you stripped it down