Inner Join Repeated Rows In Loop

[sintax63]

I'm pulling results from two tables, which I've done dozens of times now with no issues. However on this one occasion, I am getting an odd behavior where my main table is looping the results correctly, but my joined table only shows the first results over and over again.

 

======================

0001  |  Title A

0002  |  Title A

0003  |  Title A

0004  |  Title A

0005  |  Title A

 

... and so on. I've searched, googled and even yelled at the computer. Alas, I'm stuck.

 

SELECT s.course, s.datetime, s.id, s.status, d.name, d.title
FROM syllabi s
INNER JOIN schedule_desc d ON substr(s.course,0,0) = substr(d.name,0,-4)
ORDER BY s.course ASC 

 

s.course has a string length of XXXX-0000 where d.name has a string length of XXXX-0000-XXX.

 

Any thoughts?

Edited March 4, 2013 by sintax63

[Christian F.]

That's how JOINs work: For each row in the child table (the one with the FK reference) that matches the main table (the one that the FK points to), the row in the main table will be repeated.

If you want the main table's contents to only be echoed out once, then you'll need to add a conditional in the loop. Check if the current table's title is different the previous table's title. If it is, then echo the data from the main table as well.

[Barand]

INNER JOIN schedule_desc d ON s.course = SUBSTRING(d.name, 1, 9)