Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2008
    Posts
    3

    Unanswered: outer join / full join issue for mysql 5.1

    I have a small project, using PHP + Apache + DB, since the Mysql is nature fit for linux, I will consider it first, but I heard that it still premature for some of those advanced feature, like stored procedure, and join, especially the outer join / full join (no full join as far as what I know), will really like to know if someone use Mysql has the struggle to deal with the join, especially the outer join, and its performance issue, whether its stored procedure getting improved.
    Thanks.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Whoever told you that stuff is lying; MySQL has no problems with sprocs or outer joins!
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    at least recent versions don't
    forget which version of MySQL 5 (mebbe its all) has stored procedures.
    outer joins have certainly been in version 5, IIRC even earlier
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there is still no FULL OUTER JOIN in mysql, but i can show you how to achieve the same results if you show me why you need a full outer join -- i.e. show me the query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2011
    Posts
    3
    Quote Originally Posted by r937 View Post
    there is still no FULL OUTER JOIN in mysql, but i can show you how to achieve the same results if you show me why you need a full outer join -- i.e. show me the query
    I'm not sure if your offer is still valid. But I need an FULL OUTER JOIN since not all tables have the same 'domain' entries and I need 1 result with all possible domains. Right now I have the next query but all LEFT and RIGHT JOINS should be FULL OUTER JOIN:

    Code:
    SELECT domain, asd.id, asd.status, av.id, av.transport, it.id, it.transport,
    ot.id, ot.transport, ra.id, ra.restriction_class, rd.id, rd.enabled
    FROM allowed_sender_domains AS asd
    RIGHT JOIN av_transport AS av USING ( domain )
    LEFT JOIN in_transport AS it USING ( domain )
    LEFT JOIN out_transport AS ot USING ( domain )
    LEFT JOIN recipient_access AS ra USING ( domain )
    LEFT JOIN relay_domains AS rd USING ( domain )

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kamiware View Post
    ... but all LEFT and RIGHT JOINS should be FULL OUTER JOIN:
    i don't think they should, sorry

    any chance you could do a SHOW CREATE TABLE for each table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2011
    Posts
    3
    Here a dump. I replaced domain manes and IP adresses with dummy information.

    Also, every table is used by 1 program. the IDs don't have to match between them. I'm now creating a management interface where I want to put it all together.

    Code:
    CREATE TABLE IF NOT EXISTS `allowed_sender_domains` (
      `id` int(10) NOT NULL AUTO_INCREMENT,
      `domain` varchar(128) NOT NULL,
      `status` enum('OK','REJECT') NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10002 ;
    
    INSERT INTO `allowed_sender_domains` (`id`, `domain`, `status`) VALUES
    (10000, 'one.be', 'OK'),
    (10001, 'two.org', 'OK');
    (10002, 'tree.com', 'REJECT');
    
    
    CREATE TABLE IF NOT EXISTS `av_transport` (
      `id` int(10) NOT NULL AUTO_INCREMENT,
      `domain` varchar(128) NOT NULL,
      `transport` varchar(128) NOT NULL DEFAULT 'smtp:[1.2.3.x]',
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10003 ;
    
    INSERT INTO `av_transport` (`id`, `domain`, `transport`) VALUES
    (10000, 'one.be', 'smtp:[1.2.3.4]'),
    (10001, 'one.com', 'smtp:[1.2.3.5]'),
    (10002, 'four.be', 'smtp:[1.2.3.6]');
    
    
    CREATE TABLE IF NOT EXISTS `in_transport` (
      `id` int(10) NOT NULL AUTO_INCREMENT,
      `domain` varchar(128) NOT NULL,
      `transport` varchar(128) NOT NULL DEFAULT 'smtp:[1.2.3.x]',
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10003 ;
    
    INSERT INTO `in_transport` (`id`, `domain`, `transport`) VALUES
    (10000, 'one.be', 'smtp:[1.2.3.7]'),
    (10001, 'one.com', 'smtp:[1.2.3.8]'),
    (10002, 'four.be', 'smtp:[1.2.3.9]');
    
    
    CREATE TABLE IF NOT EXISTS `out_transport` (
      `id` int(10) NOT NULL AUTO_INCREMENT,
      `domain` varchar(128) NOT NULL,
      `transport` varchar(128) NOT NULL DEFAULT 'smtp:[1.2.3.x]',
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10003 ;
    
    INSERT INTO `out_transport` (`id`, `domain`, `transport`) VALUES
    (10000, 'one.be', 'smtp:[1.2.3.10]'),
    (10001, 'one.com', 'smtp:[1.2.3.11]'),
    (10002, 'four.be', 'smtp:[1.2.3.12]');
    
    
    CREATE TABLE IF NOT EXISTS `recipient_access` (
      `id` int(10) NOT NULL AUTO_INCREMENT,
      `restriction_class` enum('strong','moderate','weak') NOT NULL DEFAULT 'strong',
      `domain` varchar(128) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10003 ;
    
    INSERT INTO `recipient_access` (`id`, `restriction_class`, `domain`) VALUES
    (10000, 'strong', 'one.be'),
    (10001, 'strong', 'four.be'),
    (10002, 'strong', 'five.be');
    
    
    CREATE TABLE IF NOT EXISTS `relay_domains` (
      `id` int(10) NOT NULL AUTO_INCREMENT,
      `domain` varchar(128) NOT NULL,
      `enabled` varchar(128) NOT NULL DEFAULT 'check',
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10003 ;
    
    INSERT INTO `relay_domains` (`id`, `domain`, `enabled`) VALUES
    (10000, 'one.be', 'check'),
    (10001, 'one.com', 'check'),
    (10002, 'four.be', 'check');

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks

    try this --
    Code:
    SELECT 'asd' AS source
         , id
         , domain
         , status
         , NULL AS transport
         , NULL AS restriction_class
         , NULL AS enabled
      FROM allowed_sender_domains 
    UNION ALL
    SELECT 'av'
         , id
         , domain
         , NULL
         , transport
         , NULL
         , NULL
      FROM av_transport
    UNION ALL
    SELECT 'it'
         , id
         , domain
         , NULL
         , transport
         , NULL
         , NULL
      FROM in_transport
    UNION ALL
    SELECT 'ot'
         , id
         , domain
         , NULL
         , transport
         , NULL
         , NULL
      FROM out_transport
    UNION ALL
    SELECT 'ra'
         , id
         , domain
         , NULL
         , NULL
         , restriction_class
         , NULL
      FROM recipient_access
    UNION ALL
    SELECT 'rd'
         , id
         , domain
         , NULL
         , NULL
         , NULL
         , enabled
      FROM relay_domains
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2011
    Posts
    3
    Thanks,

    But that doesn't do what I want. I want the domains to be toghether.

    Right now I have changed the SQL to this:

    Code:
    SELECT domain, av.id, av.transport, it.id, it.transport, ot.id, ot.transport, ra.id, ra.restriction_class,
    rd.id, rd.enabled, ad.id, ad.warningrecip, ad.language
    FROM ((SELECT domain FROM av_transport) UNION (SELECT domain FROM in_transport)
    	UNION (SELECT domain FROM out_transport) UNION (SELECT domain FROM recipient_access)
    	UNION (SELECT domain FROM relay_domains) UNION (SELECT domain FROM av_db.domains)) AS dom
    LEFT JOIN av_transport AS av USING ( domain )
    LEFT JOIN in_transport AS it USING ( domain )
    LEFT JOIN out_transport AS ot USING ( domain )
    LEFT JOIN recipient_access AS ra USING ( domain )
    LEFT JOIN relay_domains AS rd USING ( domain )
    LEFT JOIN av_db.domains AS ad USING ( domain )

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nice solution

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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