[thelist] Joining tables... in MySQL

[Tris]

I've found that once tables get into the 100,000 mark, "where this is
that" is just too slow...
But You're right, I can do what I want that way, but don't wanna ;-p

call me Mr stubborn...!


On 04/04/07, Joost van Velzen <joost at nr6.nl> wrote:
> SELECT * FROM jobType, jobs WHERE jobType.id = jobs.jobType
>
> /me doesn't understand why so many people try to force themselves into
> left/inner/right joins when you can just say "where this is that"
>
> Joost van Velzen
> Webdeveloper
>
> NR6
> Plantsoengracht 6
> 1441 DE Purmerend
> T: +31 (0)299 415555
> F: +31 (0)299 415544
> E: J.vanVelzen at NR6.nl
> I: www.NR6.nl
>
>
> [èn~èr~zès] effectieve, interactieve marketing voor ambitieuze organisaties
>
> -----Oorspronkelijk bericht-----
> Van: thelist-bounces at lists.evolt.org
> [mailto:thelist-bounces at lists.evolt.org] Namens Tris
> Verzonden: woensdag 4 april 2007 16:02
> Aan: wdvltalk at lists.wdvl.com; thelist at lists.evolt.org
> Onderwerp: Re: [thelist] 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 '0000-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 <beertastic at gmail.com> 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...?? Hmmmmm
> >
> >
> >
> > --
> > 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
> --
>
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>
> --
>
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>


-- 
When a person can no longer laugh at himself, it is time for others to
laugh at him.
Thomas Szasz