Re:[wdvltalk] Joining tables... in MySQL
HERE's my SQL dump... CREATE TABLE `jobType` ( `id` int(12) NOT NULL auto_increment, `name` varchar(100) collate utf8_bin NOT NULL default '', `type` tinyint(10) NOT NULL default '0', KEY `id` (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=7 ; -- -- Dumping data for table `jobType` -- INSERT INTO `jobType` VALUES (1, 'Design', 0); INSERT INTO `jobType` VALUES (2, 'Meeting', 0); INSERT INTO `jobType` VALUES (3, 'PHP', 0); INSERT INTO `jobType` VALUES (4, 'Downtime', 0); INSERT INTO `jobType` VALUES (5, 'Copy', 0); INSERT INTO `jobType` VALUES (6, 'Other', 0); -- -- -- Table structure for table `jobs` -- CREATE TABLE `jobs` ( `id` int(12) NOT NULL auto_increment, `user` int(10) NOT NULL default '0', `week` date NOT NULL default '-00-00', `day` tinyint(3) NOT NULL default '0', `jobNo` int(10) NOT NULL default '0', `jobType` int(10) NOT NULL default '0', `hours` tinyint(5) NOT NULL default '0', KEY `id` (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=14 ; -- -- Dumping data for table `jobs` -- INSERT INTO `jobs` VALUES (4, 4, '2007-02-19', 1, 1, 1, 1); INSERT INTO `jobs` VALUES (5, 4, '2007-02-19', 2, 2, 2, 2); INSERT INTO `jobs` VALUES (6, 4, '2007-02-19', 3, 3, 3, 3); INSERT INTO `jobs` VALUES (7, 4, '2007-02-19', 4, 4, 4, 4); INSERT INTO `jobs` VALUES (8, 4, '2007-02-19', 5, 5, 5, 5); INSERT INTO `jobs` VALUES (13, 0, '2007-02-19', 0, 0, 0, 0); INSERT INTO `jobs` VALUES (12, 0, '2007-02-19', 0, 0, 0, 0); On 04/04/07, Tris [EMAIL PROTECTED] wrote: Hi Rudy.. I mean everyone ;-p i've two tables: JobType id (int) name (varChar) Jobs id (int) user (int) week (date) day (int) jobNo (int) jobType (int) hours (int) I want to list all job types, and IF there's any corresponding with the jobs table, I want to marry them up. I've used this: SELECT jobType.name, jobs.user, jobs.jobNo, jobs.jobType, jobs.hours, jobs.day FROM jobType RIGHT JOIN jobs on jobs.jobType = jobType.id But I'm only getting results if there's the Jobtype id in the jobs table.. Anyhoo... how can I list all jobTypes, and 'fill in the gaps'? I've googled OUTER joins, but Im getting errors...?? Hm -- When a person can no longer laugh at himself, it is time for others to laugh at him. Thomas Szasz -- When a person can no longer laugh at himself, it is time for others to laugh at him. Thomas Szasz � The WDVL Discussion List from WDVL.COM � To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: wdvltalk@lists.wdvl.com To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk http://www.wdvl.com ___ You are currently subscribed to wdvltalk as: archive@jab.org To unsubscribe send a blank email to [EMAIL PROTECTED] To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with.
Re: [wdvltalk] Joining tables... in MySQL
I've googled OUTER joins, but Im getting errors...?? Hm funny, google never gave me any errors, and i've been using it since it came out what you want, young paduan, is a LEFT OUTER JOIN instead ;o) � The WDVL Discussion List from WDVL.COM � To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: wdvltalk@lists.wdvl.com To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk http://www.wdvl.com ___ You are currently subscribed to wdvltalk as: archive@jab.org To unsubscribe send a blank email to [EMAIL PROTECTED] To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with.
Re: [wdvltalk] Joining tables... in MySQL
Rudy, I knew you'd be there somewhere ;-p it's odd... I'd tried that, and just got one row of results. Still do... It's going through the jobs table and only returning jobTypes that have been flagged in jobs.. not ALL job types... any other ideas? On 04/04/07, r937 [EMAIL PROTECTED] wrote: I've googled OUTER joins, but Im getting errors...?? Hm funny, google never gave me any errors, and i've been using it since it came out what you want, young paduan, is a LEFT OUTER JOIN instead ;o) • The WDVL Discussion List from WDVL.COM • To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: wdvltalk@lists.wdvl.com To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk http://www.wdvl.com ___ You are currently subscribed to wdvltalk as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with. -- When a person can no longer laugh at himself, it is time for others to laugh at him. Thomas Szasz � The WDVL Discussion List from WDVL.COM � To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: wdvltalk@lists.wdvl.com To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk http://www.wdvl.com ___ You are currently subscribed to $subst('List.Name') as: $subst('Recip.EmailAddr') To unsubscribe send a blank email to $subst('Email.UnSub') To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with.
Re: [wdvltalk] Joining tables... in MySQL
my bad, i meant padawan, not paduan see http://en.wikipedia.org/wiki/Padawan#Jedi_ranks ;o) � The WDVL Discussion List from WDVL.COM � To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: wdvltalk@lists.wdvl.com To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk http://www.wdvl.com ___ You are currently subscribed to wdvltalk as: archive@jab.org To unsubscribe send a blank email to [EMAIL PROTECTED] To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with.
Re: [wdvltalk] Joining tables... in MySQL
It's going through the jobs table and only returning jobTypes that have been flagged in jobs.. not ALL job types... i tested the LEFT OUTER JOIN on the data you supplied and it works ~fabulously~ maybe you have some other conditions that are clouding the issue � The WDVL Discussion List from WDVL.COM � To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: wdvltalk@lists.wdvl.com To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk http://www.wdvl.com ___ You are currently subscribed to wdvltalk as: archive@jab.org To unsubscribe send a blank email to [EMAIL PROTECTED] To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with.
Re: [wdvltalk] Joining tables... in MySQL
Ah, i'm on 4.1.11 that make a difference? On 04/04/07, r937 [EMAIL PROTECTED] wrote: It's going through the jobs table and only returning jobTypes that have been flagged in jobs.. not ALL job types... i tested the LEFT OUTER JOIN on the data you supplied and it works ~fabulously~ maybe you have some other conditions that are clouding the issue • The WDVL Discussion List from WDVL.COM • To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: wdvltalk@lists.wdvl.com To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk http://www.wdvl.com ___ You are currently subscribed to wdvltalk as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with. -- When a person can no longer laugh at himself, it is time for others to laugh at him. Thomas Szasz � The WDVL Discussion List from WDVL.COM � To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: wdvltalk@lists.wdvl.com To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk http://www.wdvl.com ___ You are currently subscribed to $subst('List.Name') as: $subst('Recip.EmailAddr') To unsubscribe send a blank email to $subst('Email.UnSub') To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with.
Re: [wdvltalk] Joining tables... in MySQL
Ah, i'm on 4.1.11 that make a difference? none whatsoever ;o) � The WDVL Discussion List from WDVL.COM � To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: wdvltalk@lists.wdvl.com To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk http://www.wdvl.com ___ You are currently subscribed to wdvltalk as: archive@jab.org To unsubscribe send a blank email to [EMAIL PROTECTED] To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with.
Re: [wdvltalk] Joining tables... in MySQL
:-( Here I go again... it really doesn't work... ? :-( SELECT jobType.name, jobs.user, jobs.jobNo, jobs.jobType, jobs.hours, jobs.day FROM jobType LEFT OUTER JOIN jobs ON jobs.jobType = jobType.id WHERE jobs.id IS NULL OR ( jobs.user = '4' AND jobs.week = '2007-03-19' AND ( jobs.day = '3' OR jobs.day = '0' ) ) CREATE TABLE `jobType` ( `id` int(12) NOT NULL auto_increment, `name` varchar(100) collate utf8_bin NOT NULL default '', `type` tinyint(10) NOT NULL default '0', KEY `id` (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=7 ; INSERT INTO `jobType` VALUES (1, 'Design', 0); INSERT INTO `jobType` VALUES (2, 'Meeting', 0); INSERT INTO `jobType` VALUES (3, 'PHP', 0); INSERT INTO `jobType` VALUES (4, 'Downtime', 0); INSERT INTO `jobType` VALUES (5, 'Copy', 0); INSERT INTO `jobType` VALUES (6, 'Other', 0); CREATE TABLE `jobs` ( `id` int(12) NOT NULL auto_increment, `user` int(10) NOT NULL default '0', `week` date NOT NULL default '-00-00', `day` tinyint(3) NOT NULL default '0', `jobNo` int(10) NOT NULL default '0', `jobType` int(10) NOT NULL default '0', `hours` tinyint(5) NOT NULL default '0', KEY `id` (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=19 ; INSERT INTO `jobs` VALUES (18, 4, '2007-03-05', 0, 45645, 0, 0); INSERT INTO `jobs` VALUES (4, 4, '2007-03-19', 1, 1, 1, 1); INSERT INTO `jobs` VALUES (5, 4, '2007-03-19', 2, 2, 2, 2); INSERT INTO `jobs` VALUES (6, 4, '2007-03-19', 3, 3, 3, 3); INSERT INTO `jobs` VALUES (7, 4, '2007-03-19', 4, 4, 4, 4); INSERT INTO `jobs` VALUES (8, 4, '2007-03-19', 5, 5, 5, 5); INSERT INTO `jobs` VALUES (13, 1, '2007-03-19', 2, 12, 6, 2); INSERT INTO `jobs` VALUES (12, 2, '2007-03-19', 4, 21, 3, 1); On 04/04/07, r937 [EMAIL PROTECTED] wrote: Ah, i'm on 4.1.11 that make a difference? none whatsoever ;o) • The WDVL Discussion List from WDVL.COM • To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: wdvltalk@lists.wdvl.com To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk http://www.wdvl.com ___ You are currently subscribed to wdvltalk as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with. -- When a person can no longer laugh at himself, it is time for others to laugh at him. Thomas Szasz � The WDVL Discussion List from WDVL.COM � To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: wdvltalk@lists.wdvl.com To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk http://www.wdvl.com ___ You are currently subscribed to $subst('List.Name') as: $subst('Recip.EmailAddr') To unsubscribe send a blank email to $subst('Email.UnSub') To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with.
Re: [wdvltalk] Joining tables... in MySQL
Here I go again... it really doesn't work... ? :-( yes it does, but you have to be really careful with WHERE conditions in outer joins SELECT jobType.name, jobs.user, jobs.jobNo, jobs.jobType, jobs.hours, jobs.day FROM jobType LEFT OUTER JOIN jobs ON jobs.jobType = jobType.id WHERE jobs.id IS NULL OR ( jobs.user = '4' AND jobs.week = '2007-03-19' AND ( jobs.day = '3' OR jobs.day = '0' ) ) this yields the Other jobtype, with no matching rows ;o) i'm guessing that what you really want (which you never explained) is something like this -- SELECT jobType.name , jobs.user , jobs.jobNo , jobs.jobType , jobs.hours , jobs.day FROM jobType LEFT OUTER JOIN jobs ON jobs.jobType = jobType.id AND ( ( jobs.user = 4 AND jobs.week = '2007-03-19' ) OR ( jobs.day in ( 0, 3 ) ) ) in outer joins, there is a big difference between conditions in the ON clause and conditions in the WHERE clause oh, and p.s., please do not make strings out of numbers to compare to numeric columns, it makes them soggy and hard to light ;o) � The WDVL Discussion List from WDVL.COM � To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: wdvltalk@lists.wdvl.com To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk http://www.wdvl.com ___ You are currently subscribed to wdvltalk as: archive@jab.org To unsubscribe send a blank email to [EMAIL PROTECTED] To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with.
Re: [wdvltalk] Joining tables... in MySQL
OHHH!!! Makes sence... Cheers Rudy... as ALWAYS!!! (I'd kiss you, but that'd be a bit too fruity for my taste... not that there's anything wrong with that!) On 04/04/07, r937 [EMAIL PROTECTED] wrote: Here I go again... it really doesn't work... ? :-( yes it does, but you have to be really careful with WHERE conditions in outer joins SELECT jobType.name, jobs.user, jobs.jobNo, jobs.jobType, jobs.hours, jobs.day FROM jobType LEFT OUTER JOIN jobs ON jobs.jobType = jobType.id WHERE jobs.id IS NULL OR ( jobs.user = '4' AND jobs.week = '2007-03-19' AND ( jobs.day = '3' OR jobs.day = '0' ) ) this yields the Other jobtype, with no matching rows ;o) i'm guessing that what you really want (which you never explained) is something like this -- SELECT jobType.name , jobs.user , jobs.jobNo , jobs.jobType , jobs.hours , jobs.day FROM jobType LEFT OUTER JOIN jobs ON jobs.jobType = jobType.id AND ( ( jobs.user = 4 AND jobs.week = '2007-03-19' ) OR ( jobs.day in ( 0, 3 ) ) ) in outer joins, there is a big difference between conditions in the ON clause and conditions in the WHERE clause oh, and p.s., please do not make strings out of numbers to compare to numeric columns, it makes them soggy and hard to light ;o) • The WDVL Discussion List from WDVL.COM • To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: wdvltalk@lists.wdvl.com To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk http://www.wdvl.com ___ You are currently subscribed to wdvltalk as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with. -- When a person can no longer laugh at himself, it is time for others to laugh at him. Thomas Szasz � The WDVL Discussion List from WDVL.COM � To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: wdvltalk@lists.wdvl.com To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk http://www.wdvl.com ___ You are currently subscribed to $subst('List.Name') as: $subst('Recip.EmailAddr') To unsubscribe send a blank email to $subst('Email.UnSub') To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with.