On Mon, Jun 02, 2003 at 12:48:38PM +0200, Dejan Milenkovic wrote:
> I have two tables, one is containing data about courses and the second one
> is containing data about course start date.
> Is it possible to list all courses with one query which should also return
> earliest scheduled start dates for courses (which are in the second table).
> I tried something like this:
> SELECT c_d.*, MIN(c_s.start) as start FROM course_data c_d RIGHT OUTER JOIN
> course_start c_s ON c_s.courseid=c_d.courseid WHERE c_s.start>NOW() GROUP BY
> c_s.courseid
> But this return only courses that have start date if I replace ON condition
> with 1=1 I get list of all courses but with the same date, if I remove ON
> condition MySQL return error, I also tried replacing ON
> c_s.courseid=c_d.courseid with
> USING (courseid) but that didn't help.
> Any help is appriciated.
>
> Here are the table definitions and test data.
> CREATE TABLE course_data (
> courseid mediumint(9) NOT NULL auto_increment,
> data varchar(255) NOT NULL default '',
> PRIMARY KEY (courseid)
> ) TYPE=MyISAM;
> INSERT INTO course_data VALUES (1, 'Test data');
> INSERT INTO course_data VALUES (2, 'Also test data');
>
> CREATE TABLE course_start (
> courseid mediumint(9) NOT NULL,
> start date NOT NULL default '0000-00-00'
> ) TYPE=MyISAM;
> INSERT INTO course_start VALUES (1, '2004-12-12');
>
> So I need quey that would return both courses where the start column for
> course 1 would be '2004-12-1 and NULL for course 2.
You don't want a RIGHT OUTER JOIN -- you want a LEFT [OUTER] JOIN,
because you are trying to get a result for each row from the left-most
table. But you also need to explicitly select those results where the
start is NULL. So your query would be:
SELECT c_d.*, MIN(c_s.start) AS start
FROM course_data c_d
LEFT JOIN course_start c_s USING (courseid)
WHERE c_s.start > NOW()
OR c_s.start IS NULL
GROUP BY c_s.courseid;
This returns:
+----------+----------------+------------+
| courseid | data | start |
+----------+----------------+------------+
| 2 | Also test data | NULL |
| 1 | Test data | 2004-12-12 |
+----------+----------------+------------+
Here's an article from SQL-Guru.com that explains the basic join types:
http://www.sql-guru.com/sql101/basicjoins.html
I hope that helps.
Jim Winstead
MySQL AB
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
