joining two tables to get value of unique ID

[bazz]

Hello again,

trying to select a value (PK) from one table and join to another table, getting the value of the item with that ID. I seem only to be gettinbg the unique ID value and not the other value in the record.


("SELECT Business_Type_ID From tbl_BusinessDetails table inner join tbl_businessType
on Business_Type_ID = Type_ID");



here's the table structure


tbl_BusinessType
| Type_ID | Business_Type |

tbl_BusinessDetails
| Business_ID | Business_Name| Business_Type_ID |



I am trying to get the value of Business_Type (from tbl_BusinessType) using the PK (Business_Type_ID) in the tbl_BusinessDetails.

I also do not want any matches.

A 'leg up' into MYSQL would be really appreciated

bazz

[Fumigator]

It all looks fine, except you need to include the columns in your SELECT clause that you want to return. You're only selecting business_type_id... add business_type.

[TheShaner]

SELECT BD.Business_ID, BD.Business_Name, BT.Business_Type
FROM tbl_BusinessDetails BD
INNER JOIN tbl_businessType BT
ON BD.Business_Type_ID = BT.Type_ID
The above query will pull the Business ID and Business Name from the Business Details table plus the associated Business Type from the Business Type table.

Also, in tbl_BusinessDetails, your primary key should be Business_ID and not Business_Type_ID, as Business_ID is your distinguishing and unique field in that table. The Business_Type_ID will end up duplicating in your records due to more than one business having the same business type, thus it will not work as a primary key.

-Shane

[bazz]

doesn't seem to work. it returns just the value '0' instaed of either the ID (00000000001), or the type (Bed and Breakfast).


("SELECT Business_Type_ID AND Business_Type From $table inner join tbl_businessType
on Business_Type_ID = Type_ID");

Probably because there is no filed in that table for Business_Type. I also have tried (wrongly, I know),


("SELECT Business_Type_ID From $table AND Business_Type from tbl_businessType inner join tbl_businessType
on Business_Type_ID = Type_ID");



bazz

[Fumigator]

Use a comma rather than "AND".

SELECT columnName1, columnName2
...


You also need to make sure that the primary key in the one table is the same column type (int, char, etc) as the foreign key in the other table. (Don't mix char with int, for example)

[bazz]

aha, I might have got it.


("SELECT Business_Type_ID, Business_Type From $table left outer join tbl_businessType
on Business_Type_ID = Type_ID");

I obviously need to read up on inner and outer. i thought I had it.

I have read about getting into bad habits. So, how is the code from that perspective plz?

bazz

[Fumigator]

I don't see any glaring bad habits. I like to use alias names with joins to keep it straight which columns are coming from which tables, as Theshaner has above.


// the alias for table1 is "a"
// the alias for table2 is "b"
SELECT a.columnName, b.columnName
FROM table1 as a
JOIN table2 as b
ON a.key = b.fkey
WHERE a.columnName = 'whatever'

[TheShaner]

I gave you the query that would do it above.

Anyway, using a LEFT JOIN means that if a record in your Business Details does not have a match in the Business Type table, it will still select that record, but put a NULL value in the Business Type field. With the way you have that query set up and only selecting the values that match and pull from the Business Type table, this LEFT JOIN will result in possible blank results in your query.

-Shane

[bazz]

Oops, Shane, I didn't see that earlier post coz I was probably typing my own post.



Also, in tbl_BusinessDetails, your primary key should be Business_ID and not Business_Type_ID, as Business_ID is your distinguishing and unique field in that table.


Actually, it is though I see how I caused confusion in my first post. I meant FK and not PK, becasue it is only the PK in the other table, tbl_businessType.

Thank you both for your help. I may have to call on your kindness again soon.

bazz