subject:"Re\: \[wdvltalk\] Joining tables... in MySQL"

Re:[wdvltalk] Joining tables... in MySQL

2007-04-04 Thread Tris


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

2007-04-04 Thread r937


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

2007-04-04 Thread Tris


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

2007-04-04 Thread r937


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

2007-04-04 Thread r937


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

2007-04-04 Thread Tris


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

2007-04-04 Thread r937


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

2007-04-04 Thread Tris


:-(

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

2007-04-04 Thread r937


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

2007-04-04 Thread Tris


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.

Re:[wdvltalk] Joining tables... in MySQL

Re: [wdvltalk] Joining tables... in MySQL

Re: [wdvltalk] Joining tables... in MySQL

Re: [wdvltalk] Joining tables... in MySQL

Re: [wdvltalk] Joining tables... in MySQL

Re: [wdvltalk] Joining tables... in MySQL

Re: [wdvltalk] Joining tables... in MySQL

Re: [wdvltalk] Joining tables... in MySQL

Re: [wdvltalk] Joining tables... in MySQL

Re: [wdvltalk] Joining tables... in MySQL

10 matches

Site Navigation

Mail list logo

Footer information