Tom Tom - 4 months ago 8
MySQL Question

SQL Joins stop working when table columns are defined

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

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.