CODEWITHSUNDEEP
mysqlsql
Brandyn
Brandyn

Reputation: 1017

querying primary keys from a foreign key

I'm fairly new to MySQL and I am struggling querying or referencing primary key's by foreign keys specified in a table. Can someone please explain how to do this in fairly simple terms, as everything I've searched is a bit hard to understand considering majority of others have allot more MySQL knowledge then me.

Let's just assume these are the tables:

CREATE TABLE IF NOT EXISTS customer(
    custID int AUTO_INCREMENT,
    custLname varchar(30),
    custAdd varchar(100),
    custSuburb varchar(30),
    custPcode varchar(4),
    custState varChar(20),
    custPhone varchar(10),
    PRIMARY KEY (custID)
)ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS tour(
    DailyTourID int,
    tourDate DATE,
    tourTime TIME,
    tourName varchar(30),
    tourDriverID int,
    tourBusID varchar(2),
    PRIMARY KEY (DailyTourID, tourDate),
) ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS TourCustLink(
    TourCustLinkID int AUTO_INCREMENT,
    TourID int,
    TourDate DATE,
    customerID int,
    PRIMARY KEY (TourCustLinkID),
    FOREIGN KEY (TourID, TourDate) REFERENCES tour(DailyTourID, tourDate),
    FOREIGN KEY (customerID) REFERENCES customer(custID)
) ENGINE = INNODB;

I am aware this is a pretty bad example, but lets say I want to show the custLname, custAdd, custPhone and tourDates for each customer. How would I accomplish this?

Upvotes: 1

Views: 47

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

JOIN them:

SELECT 
  c.custLname,
  c.custAdd,
  c.custPhone,
  t.TourDate,
  t.TourName,
  ...
FROM TourCustLink   AS tl 
INNER JOIN Customer AS c  ON tl.customerId = c.custID
INNER JOIN Tour     AS t  ON tl.tourID     = t.DailyTourID 
                         AND tl.tourDate   = t.tourDate;

You might also need to use OUTER JOINs (LEFT JOIN or RIGHT JOIN) instead of INNER JOIN, in case you want to include those unmatched rows.

For more information about the JOIN types see this article:

Upvotes: 1

Related Questions