outer join / full join issue for mysql 5.1

dragondad · #1 (**permalink**) 08-06-08, 09:57

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.

gvee · #2 (**permalink**) 08-06-08, 10:21

Whoever told you that stuff is lying; MySQL has no problems with sprocs or outer joins!

healdem · #3 (**permalink**) 08-06-08, 11:07

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

r937 · #4 (**permalink**) 08-06-08, 11:12

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

kamiware · #5 (**permalink**) 04-06-11, 05:36

Quote:

Originally Posted by r937

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 )

r937 · #6 (**permalink**) 04-06-11, 06:45

Quote:

Originally Posted by kamiware

... 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?

kamiware · #7 (**permalink**) 04-06-11, 08:32

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');

r937 · #8 (**permalink**) 04-06-11, 10:02

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

kamiware · #9 (**permalink**) 04-06-11, 10:17

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 )

r937 · #10 (**permalink**) 04-06-11, 10:29

nice solution

Internet Services

Web Development

Digital Marketing

Consumer Tech