SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Left Join syntax

    Hi there, I need ur help.

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

    Code:
    SELECT A.*, CA.*
    FROM _mytable1 A LEFT JOIN _mytable2 CA 
    ON CA._CE = A._CE
    And this output:
    Code:
    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?

    Code:
    /*
    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. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,000
    Mentioned
    101 Post(s)
    Tagged
    0 Thread(s)
    Is this what your looking for:

    Code SQL:
    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).
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Thanks You Sir !


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •