Left Join syntax

Databases
1

Hi there, I need ur help.

I have three tables in MySQL and this is my query LEFT JOIN:


SELECT A.*, CA.*
FROM _mytable1 A LEFT JOIN _mytable2 CA 
ON CA._CE = A._CE

And this output:


xID	_CE		Name	DT		xID	N_V	myDate		_CE
10288	872800000	GUER	2008-09-01	1	14775	2011-03-03	872800000
10289	863566231	FERR	2010-05-31	2	14776	2008-09-16	863566231
10290	855797950	AVAL	2010-02-28	3	14777	2008-12-04	855797950

But now I need extract the xID field of the _mytable3… can u help me?


/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50144
Source Host           : localhost:3306
Source Database       : ccom714

Target Server Type    : MYSQL
Target Server Version : 50144
File Encoding         : 65001

Date: 2011-03-31 21:18:23
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `_mytable1`
-- ----------------------------
DROP TABLE IF EXISTS `_mytable1`;
CREATE TABLE `_mytable1` (
  `xID` int(10) NOT NULL AUTO_INCREMENT,
  `_CE` varchar(255) DEFAULT NULL,
  `Name` varchar(255) DEFAULT NULL,
  `DT` date DEFAULT NULL,
  PRIMARY KEY (`xID`)
) ENGINE=InnoDB AUTO_INCREMENT=10291 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of _mytable1
-- ----------------------------
INSERT INTO _mytable1 VALUES ('10288', '872800000', 'GUER', '2008-09-01');
INSERT INTO _mytable1 VALUES ('10289', '863566231', 'FERR', '2010-05-31');
INSERT INTO _mytable1 VALUES ('10290', '855797950', 'AVAL', '2010-02-28');


/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50144
Source Host           : localhost:3306
Source Database       : ccom714

Target Server Type    : MYSQL
Target Server Version : 50144
File Encoding         : 65001

Date: 2011-03-31 21:18:28
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `_mytable2`
-- ----------------------------
DROP TABLE IF EXISTS `_mytable2`;
CREATE TABLE `_mytable2` (
  `xID` int(10) NOT NULL AUTO_INCREMENT,
  `N_V` varchar(255) DEFAULT NULL,
  `myDate` date DEFAULT NULL,
  `_CE` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`xID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of _mytable2
-- ----------------------------
INSERT INTO _mytable2 VALUES ('1', '14775', '2011-03-03', '872800000');
INSERT INTO _mytable2 VALUES ('2', '14776', '2008-09-16', '863566231');
INSERT INTO _mytable2 VALUES ('3', '14777', '2008-12-04', '855797950');


/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50144
Source Host           : localhost:3306
Source Database       : ccom714

Target Server Type    : MYSQL
Target Server Version : 50144
File Encoding         : 65001

Date: 2011-03-31 21:18:33
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `_mytable3`
-- ----------------------------
DROP TABLE IF EXISTS `_mytable3`;
CREATE TABLE `_mytable3` (
  `xID` int(10) NOT NULL AUTO_INCREMENT,
  `myDate` date DEFAULT NULL,
  `_CE` varchar(255) DEFAULT NULL,
  `DT` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`xID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of _mytable3
-- ----------------------------
INSERT INTO _mytable3 VALUES ('1', '2011-03-03', '855797950', '2011-06-03');
INSERT INTO _mytable3 VALUES ('2', '2011-03-03', '657164259', '2011-05-03');
INSERT INTO _mytable3 VALUES ('3', '2011-03-03', '872800000', '2011-04-03');
INSERT INTO _mytable3 VALUES ('4', '2011-03-03', '617869161', '2011-03-03');
INSERT INTO _mytable3 VALUES ('5', '2011-03-03', '863566231', '2011-02-03');
2

Is this what your looking for:

SELECT
      A.*
    , CA.*
    , t3.*
FROM
    _mytable1 AS A
LEFT JOIN
    _mytable2 AS CA
        ON A._CE = CA._CE
LEFT JOIN
    _mytable3 AS t3
        ON CA._CE = t3._CE

A couple of tips:

  1. When working with any select that is in the main query, try to avoid using * only select the fields from the tables that you need.
  2. Try to give tables names that mean/relate to what is being stored in that table (chances are that the table names aren’t the actual table names that your using).
3

Thanks You Sir !