SQL Joins stop working when table columns are defined

Question

I am using sql to create a custom ODBC query into Excel.

I have 2 tables 'TBL_CONTACT' and 'TBL_ADDRESS' that I want to INNER JOIN on 'contactid', which is a column that exists in both.

This works fine if I select all columns, but stops working if I start defining the columns I want to show.

Working code:

SELECT * FROM "TBL_CONTACT" AS CONTACT

INNER JOIN (SELECT * FROM "TBL_ADDRESS" AS ADDRESS) ADDRESS
ON CONTACT.contactid = ADDRESS.contactid

Non-working code:

SELECT CONTACT.contactid, CONTACT.fullname FROM "TBL_CONTACT" AS CONTACT

INNER JOIN (SELECT * FROM "TBL_ADDRESS" AS ADDRESS) ADDRESS
ON CONTACT.contactid = ADDRESS.contactid

This only shows the 'contactid' and 'fullname' columns from 'TBL_CONTACT' and doesn't join anything from 'TBL_ADDRESS'.

EXAMPLE:

TBL_CONTACT
contactid   firstname   fullname
1001        John        John Smith
1002        Tom         Tom Adams

TBL_ADDRESS
contactid   line1       line2        postcode
1001        3 Farm Ln   Essex        AB1 1BA
1002        1 Tim st    Kent         CN2 2NC

Desired result:

contactid   fullname     contactid2  line1       line2    postcode
1001        John Smith   1001        3 Farm Ln   Essex    AB1 1BA
1002        Tom Adams    1002        1 Tim st    Kent     CN2 2NC

1st code gives following result:

contactid   firstname    fullname     contactid2  line1       line2    postcode
1001        John         John Smith   1001        3 Farm Ln   Essex    AB1 1BA
1002        Tom          Tom Adams    1002        1 Tim st    Kent     CN2 2NC

2nd code gives following result:

contactid   fullname
1001        John Smith
1002        Tom Adams

I'm fairly new to SQL but am not sure what i'm doing wrong here. Any help would be greatly appreciated!

Answer 1

Joining a table results in the columns being available for your select. When you select all columns (with * like in your first code snippet) the columns of your joined table will be selected, too. However, when manually selecting columns, you have to define which columns you want from your joined table, too.

Let's say you want to have the 2 columns contactid and fullname from your Table TBL_CONTACT and all columns from the joined table TBL_ADDRESS:

SELECT `TBL_CONTACT`.`contactid`, `TBL_CONTACT`.`fullname`, `TBL_ADDRESS`.* FROM TBL_CONTACT 
INNER JOIN TBL_ADDRESS
ON `TBL_CONTACT`.`contactid` = `TBL_ADDRESS`.`contactid` 

Backticks are optional.

Alternatively, you could specify columns for the joined table, too.