[thelist] Joining tables... in MySQL

Joost van Velzen joost at nr6.nl
Wed Apr 4 09:07:34 CDT 2007


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 ! 




More information about the thelist mailing list