CODEWITHSUNDEEP
mysqlsql
Luke CheerfulPlum Pace
Luke CheerfulPlum Pace

Reputation: 293

MySQL Understanding Basic Joins

I am struggling to understand basic MySQL joins.

Basically I've got 2 tables one with a customer first name and address id in it and another with the actual address.

Rather than just displaying the customer name and an address id I want it to display the customer name and the actual address.

My basic select statement is like this:

SELECT firstName, addressId FROM Customer

It will display something like this:

firstName   addressId
---------------------
Bob         56

Rather than than I would like to join the addressId with an actual address in another table

Like this:

firstName    address
----------------------------------
Bob          45 Somewhere street

Is there anyone who can show me the best way to achieve this?

Also can anyone recommend a good tutorial for joins?

Upvotes: 3

Views: 1291

Answers (6)

John Woo
John Woo

Reputation: 263693

SELECT  a.name, b.address
FROM    Customer a INNER JOIN AddressList b on a.addressID = b.addressID

To learn more about joins, see the article below,

Upvotes: 4

TheRedOne
TheRedOne

Reputation: 165

Assuming that both tables have an address id, you could use

SELECT firstname, address, from table1 JOIN table2 
ON table1.addressid = table2.addressid

Upvotes: 0

ethrbunny
ethrbunny

Reputation: 10469

Put the same 'addressid' in both the name and address tables then join the two as:

select name, address from customer join addresses 
on customer.addressid = addresses.addressid;

Upvotes: 0

Vishal Suthar
Vishal Suthar

Reputation: 17193

It would be:

SELECT firstName, address FROM Address As A 
INNER JOIN Customer as C ON C.addressId=A.addressId

Visual Representation of JOINS

Upvotes: 0

Mariappan Subramanian
Mariappan Subramanian

Reputation: 10063

Your condition is inner join, This is the simplest, most understood Join and is the most common. This query will return all of the records in the left table (Customer) that have a matching record in the right table (address). This Join is written as follows: 

 SELECT firstName, address FROM Customer 
 INNER JOIN address ON Customer.addressId=address.addressId

SQL_LIVE_DEMO

Sample Output :

FIRSTNAME       ADDRESS
  Bob       45 Somewhere street

Upvotes: 3

Stevan Trajkoski
Stevan Trajkoski

Reputation: 310

Lets say that you have following tables:

Customer(ID, FName, LName, AddressID)
Address(AddressID, Streeet, HNUmber, City)

This will display customers address instead of AddressID:

SELECT c.ID, c.Fname, c.LName, a.Street, a.HNumber, a.City
FROM Customer c, Address a
WHERE
  c.AddressID = a.AddressID

Upvotes: 0

Related Questions