MySQL SELECT and return unknown number of related foreign keys

I have two tables in a database that look like this

Course:

• ixCourse (primary key)
• enType (enum: 3 possible values)
• sCourse (string)
• sTitle (string)
• sWhen (string)
• sDescription (string)

Item:

• ixItem (primary key)
• ixCourse (foreign key references Course.ixCourse)
• bLink (boolean)
• sAddress (string)
• sDescription (string)

In this setup, I can have a given Course and I can have multiple related Items for any Course. There is no uniform number of Items per Course. Do I need to perform a separate query to pull all of the related Items for each Course or ca开发者_StackOverflow中文版n I do this in 1 select statement without returning redundant information?

without returning redundant information? If you mean without repeating the Course Details, then no - not easily in one query. There are some horrendous queries (in terms of both the idea and the code) to return the following: but I wouldn't advice exploring it.

ixCourse, enType .. , ixItem, sDescription
1         1           1       first for course 1
                      2       Second for course 1   << notice no course details
2         1           3       first for course #2

But the normal practice is to JOIN between the two producing

ixCourse, enType .. , ixItem, sDescription
1         1           1       first for course 1
1         1           2       Second for course 1   << course details repeated per item
2         1           3       first for course #2

The query for such would be

select c.*, i.*
from course c
left join item i on c.ixCourse = i.ixCourse

Here I used LEFT JOIN, which could produce this (e.g. course 2 has no items)

ixCourse, enType .. , ixItem, sDescription
1         1           1       first for course 1
1                     2       Second for course 1   << course details repeated
2         1           NULL    NULL                  << nothing for item columns
3         1           4       first for course #3

Changing it to INNER JOIN (or abbreviated JOIN) will remove the courses that have no description.

Just use a join:

SELECT * FROM Course C
JOIN Item I ON C.ixCourse=I.ixCourse

Yes, you will get the course info repeated.. there's no way to just return one row of course data and then multiple item rows without doing two queries. You can return just one course's worth of items in one query if that's what you're asking..

It's not clear what you want as output, but this:

   SELECT c.*, i.*
     FROM COURSE c
LEFT JOIN ITEM i ON i.ixcourse = c.ixcourse

...will return all courses, and any items related to the course. If a course doesn't have items related to it, the ITEM columns in the output will be NULL. That said, the course columns will hold duplicate values if there is more than one item related to it -- but the ITEM columns will be different.

If you only want a list of courses that have items, use:

SELECT c.*, i.*
  FROM COURSE c
  JOIN ITEM i ON i.ixcourse = c.ixcourse

What's a JOIN?

This is a good primer on JOINs, and the differences between them.