Inner Join failed

[jd22]

Hi i have a sql query like:

 

SELECT FirstName, LastName, CourseName, LessonStart, Weekday, Email, mMail, s.Postcode, Prefecture, City, Town, AddressLine1, AddressLine2 FROM Student s 
LEFT JOIN Enrol en ON s.StudentID = en.StudentID 
LEFT JOIN SchCourse sc ON en.SchCourseID = sc.SchCourseID 
LEFT JOIN Course c ON sc.CourseID = c.CourseID 
LEFT JOIN FEnrol fe ON c.CourseID = fe.CourseID 
LEFT JOIN Address a ON s.Postcode = a.Postcode 
[b]LEFT JOIN KidsStudent ks ON s.StudentID = ks.StudentID  << This is where the problem[/b]
WHERE DateOfBirth = '0000-00-00' AND DateOfBirth BETWEEN 2001-01-01 AND 1995-12-31 GROUP BY FirstName ORDER BY FirstName ASC

 

If i remove bold part then query works fine as it should, unfortunately if i add that part query return empty result(But there is data)

 

What did i do wrong?

[AyKay47]

have you tried using mysql_error() to output the MySQL error?

 

$sql = "SELECT FirstName, LastName, CourseName, LessonStart, Weekday, Email, mMail, s.Postcode, Prefecture, City, Town, AddressLine1, AddressLine2 FROM Student s 
LEFT JOIN Enrol en ON s.StudentID = en.StudentID 
LEFT JOIN SchCourse sc ON en.SchCourseID = sc.SchCourseID 
LEFT JOIN Course c ON sc.CourseID = c.CourseID 
LEFT JOIN FEnrol fe ON c.CourseID = fe.CourseID 
LEFT JOIN Address a ON s.Postcode = a.Postcode 
LEFT JOIN KidsStudent ks ON s.StudentID = ks.StudentID
WHERE DateOfBirth = '0000-00-00' AND DateOfBirth BETWEEN 2001-01-01 AND 1995-12-31 GROUP BY FirstName ORDER BY FirstName ASC";
mysql_query($sql) or die(mysql_error());

[jd22]

have you tried using mysql_error() to output the MySQL error?

 

Yes, i did my there is no thing but blank page. No output, no warning.

[AyKay47]

since you have multiple and statements, i would use parenthesis around your BETWEEN clause..

 

$sql = "SELECT FirstName, LastName, CourseName, LessonStart, Weekday, Email, mMail, s.Postcode, Prefecture, City, Town, AddressLine1, AddressLine2 FROM Student s 
LEFT JOIN Enrol en ON s.StudentID = en.StudentID 
LEFT JOIN SchCourse sc ON en.SchCourseID = sc.SchCourseID 
LEFT JOIN Course c ON sc.CourseID = c.CourseID 
LEFT JOIN FEnrol fe ON c.CourseID = fe.CourseID 
LEFT JOIN Address a ON s.Postcode = a.Postcode 
LEFT JOIN KidsStudent ks ON s.StudentID = ks.StudentID
WHERE DateOfBirth = '0000-00-00' AND (DateOfBirth BETWEEN 2001-01-01 AND 1995-12-31) GROUP BY FirstName ORDER BY FirstName ASC";

 

also, you need to use a table prefix for the DateOfBirth field.

[jd22]

since you have multiple and statements, i would use parenthesis around your BETWEEN clause..

also, you need to use a table prefix for the DateOfBirth field.

 

Dear AyKay47 Thanks for your reply.

Query doesn't work even without WHERE....... clause.

 

It works perfect for

SELECT FirstName, LastName, CourseName, LessonStart, Weekday, Email, mMail, s.Postcode, Prefecture, City, Town, AddressLine1, AddressLine2 FROM Student s 
LEFT JOIN Enrol en ON s.StudentID = en.StudentID 
LEFT JOIN SchCourse sc ON en.SchCourseID = sc.SchCourseID 
LEFT JOIN Course c ON sc.CourseID = c.CourseID 
LEFT JOIN FEnrol fe ON c.CourseID = fe.CourseID 
LEFT JOIN Address a ON s.Postcode = a.Postcode GROUP BY FirstName ORDER BY FirstName ASC

 

But adding

LEFT JOIN KidsStudent ks ON s.StudentID = ks.StudentID 

and/or adding

WHERE DateOfBirth = '0000-00-00' AND DateOfBirth BETWEEN 2001-01-01 AND 1995-12-31

doesn't work

 

[AyKay47]

try both suggestions in my previous post.. if it still doesn't work.. post your new code and i will look into it further..

[jd22]

Dear AyKay47

 

Here is entire code.

Ir is not working either in phpmyadmin or in embedded php file.

 

I really appreciate for you trying help.

SELECT FirstName, LastName, CourseName, LessonStart, Weekday, Email, mMail, s.Postcode, Prefecture, City, Town, AddressLine1, AddressLine2 FROM Student s 
LEFT JOIN Enrol en ON s.StudentID = en.StudentID 
LEFT JOIN SchCourse sc ON en.SchCourseID = sc.SchCourseID 
LEFT JOIN Course c ON sc.CourseID = c.CourseID 
LEFT JOIN FEnrol fe ON c.CourseID = fe.CourseID 
LEFT JOIN Address a ON s.Postcode = a.Postcode GROUP BY FirstName ORDER BY FirstName ASC
INNER JOIN KidsStudent k ON s.StudentID = k.StudentID 
WHERE k.DateOfBirth = '0000-00-00' AND (k.DateOfBirth BETWEEN 2001-01-01 AND 1995-12-31) 
GROUP BY FirstName ORDER BY FirstName ASC

[AyKay47]

well the problem here obviously isn't a syntax error since you are not receiving an error from the mysql server.. the problem is that the query is not grabbing any rows due to your main WHERE clause..

I notice that you also need quotes around your two dates in this line. like so

 

WHERE k.DateOfBirth = '0000-00-00' AND (k.DateOfBirth BETWEEN '2001-01-01' AND '1995-12-31') 

 

[jd22]

Adding quotes also has no affect.

But same query works fine if remove part below  :confused:

I am doing some mistakes but could not find where. Almost 3am and still working one query  :(

 

LEFT JOIN KidsStudent ks ON s.StudentID = ks.StudentID 
WHERE k.DateOfBirth = '0000-00-00' AND (k.DateOfBirth BETWEEN '2001-01-01' AND '1995-12-31') 

[/code]

[AyKay47]

your where clause strikes me as invalid.. how could "DateOfBirth" equal "0000-00-00" AND be between those two dates?

perhaps you meant an OR instead of AND.. as both of those conditions cannot be true at once

[jd22]

Query doesn't work even without WHERE statement.

 

it was work when it was

SELECT FirstName, LastName, CourseName, LessonStart, Weekday, Email, mMail, s.Postcode, Prefecture, City, Town, AddressLine1, AddressLine2 FROM Student s

LEFT JOIN Enrol en ON s.StudentID = en.StudentID

LEFT JOIN SchCourse sc ON en.SchCourseID = sc.SchCourseID

LEFT JOIN Course c ON sc.CourseID = c.CourseID

LEFT JOIN FEnrol fe ON c.CourseID = fe.CourseID

LEFT JOIN Address a ON s.Postcode = a.Postcode GROUP BY FirstName ORDER BY FirstName ASC

GROUP BY FirstName ORDER BY FirstName ASC

 

but it is not working now after change(added bold part) into this

SELECT FirstName, LastName, CourseName, LessonStart, Weekday, Email, mMail, s.Postcode, Prefecture, City, Town, AddressLine1, AddressLine2 FROM Student s

LEFT JOIN Enrol en ON s.StudentID = en.StudentID

LEFT JOIN SchCourse sc ON en.SchCourseID = sc.SchCourseID

LEFT JOIN Course c ON sc.CourseID = c.CourseID

LEFT JOIN FEnrol fe ON c.CourseID = fe.CourseID

LEFT JOIN Address a ON s.Postcode = a.Postcode GROUP BY FirstName ORDER BY FirstName ASC

INNER JOIN KidsStudent k ON s.StudentID = k.StudentID

WHERE k.DateOfBirth = '0000-00-00' AND (k.DateOfBirth BETWEEN 2001-01-01 AND 1995-12-31)

GROUP BY FirstName ORDER BY FirstName ASC

[AyKay47]

i have already told you that the WHERE statement you have will not work..

i will rewrite this for you

 

$sql = "SELECT table.FirstName, table.LastName, table.CourseName, table.LessonStart, table.Weekday, table.Email, table.mMail, table.Postcode, table.Prefecture, table.City, table.Town, table.AddressLine1, table.AddressLine2 FROM Student 
LEFT JOIN (Enrol,SchCourse,Course,FEnrol,Address,KidsStudent) ON (table.StudentID=table.StudentID AND table.SchCourse = table.SchCourseID AND table.CourseID = table.CourseID AND table.CourseID = table.CourseID AND table.Postcode = table.Postcode AND table.KidsStudent = table.StudentID) 
WHERE table.DateOfBirth = '0000-00-00' OR (table.DateOfBirth BETWEEN '2001-01-01' AND '1995-12-31') 
GROUP BY table.FirstName ORDER BY table.FirstName ASC";

 

replace "table" with the correct table names.. no aliases

[jd22]

Dear AyKay47

Seems have no luck.

I don't understand why query cannot grab records after adding one more table? Student.StudentID and KidsStudent.StudentID is related.

Oh Lord !! it is 5am

[fenway]

That's becuase your BETWEEN is wrong -- needs to go from low to hi.

[jd22]

That's becuase your BETWEEN is wrong -- needs to go from low to hi.

Okay. I got what you mean. It is my fault.

But query return no data. Actually there is data in DB. Students tables holds all students(adults and kids) KidsStudents holds only kids.

Query works perfect without joining KidsStudent table. However, it is not grabbing data after inner join kidsstudent.

 

I have to use Student.StudentID twice if i want to add kidsstudent. Do you think that makes trouble?

 

SELECT FirstName, LastName, CourseName, LessonStart, Weekday, Email, mMail, s.Postcode, Prefecture, City, Town, AddressLine1, AddressLine2 FROM Student s

LEFT JOIN Enrol en ON s.StudentID = en.StudentID

LEFT JOIN SchCourse sc ON en.SchCourseID = sc.SchCourseID

LEFT JOIN Course c ON sc.CourseID = c.CourseID

LEFT JOIN FEnrol fe ON c.CourseID = fe.CourseID

LEFT JOIN Address a ON s.Postcode = a.Postcode GROUP BY FirstName ORDER BY FirstName ASC

INNER JOIN KidsStudent k ON s.StudentID = k.StudentID

WHERE k.DateOfBirth = '0000-00-00' AND (k.DateOfBirth BETWEEN 2001-01-01 AND 1995-12-31)

GROUP BY FirstName ORDER BY FirstName ASC

[awjudd]

Do you have any records in your KidsStudent table for any of the people within the Student table?

 

The fact that you are referencing the same column twice makes no difference in the query.  What happens when you remove your WHERE clause?  Do you get results?

 

~juddster

[jd22]

Do you have any records in your KidsStudent table for any of the people within the Student table?

 

The fact that you are referencing the same column twice makes no difference in the query.  What happens when you remove your WHERE clause?  Do you get results?

 

~juddster

Yes, I have about 20 records in KidsStudent column. Matter of fact that KidsStudent Column just hold student's birthday, emergency contact and parent's name and linked to Student table with StudentID primary key

 

Query Works

SELECT FirstName, LastName, CourseName, LessonStart, Weekday, Email, mMail, s.Postcode, Prefecture, City, Town, AddressLine1, AddressLine2 FROM Student s
LEFT JOIN Enrol en ON s.StudentID = en.StudentID
LEFT JOIN SchCourse sc ON en.SchCourseID = sc.SchCourseID
LEFT JOIN Course c ON sc.CourseID = c.CourseID
LEFT JOIN FEnrol fe ON c.CourseID = fe.CourseID
LEFT JOIN Address a ON s.Postcode = a.Postcode GROUP BY FirstName ORDER BY FirstName ASC

 

Query Works

SELECT FirstName, LastName, CourseName, LessonStart, Weekday, Email, mMail, s.Postcode, Prefecture, City, Town, AddressLine1, AddressLine2 FROM Student s
INNER JOIN KidsStudent k ON s.StudentID = k.StudentID  GROUP BY FirstName ORDER BY FirstName ASC

 

Query doesn't Works

SELECT FirstName, LastName, CourseName, LessonStart, Weekday, Email, mMail, s.Postcode, Prefecture, City, Town, AddressLine1, AddressLine2 FROM Student s
LEFT JOIN Enrol en ON s.StudentID = en.StudentID
LEFT JOIN SchCourse sc ON en.SchCourseID = sc.SchCourseID
LEFT JOIN Course c ON sc.CourseID = c.CourseID
LEFT JOIN FEnrol fe ON c.CourseID = fe.CourseID
LEFT JOIN Address a ON s.Postcode = a.Postcode GROUP BY FirstName ORDER BY FirstName ASC
INNER JOIN KidsStudent k ON s.StudentID = k.StudentID
GROUP BY FirstName ORDER BY FirstName ASC

 

Query doesn't Works

SELECT FirstName, LastName, CourseName, LessonStart, Weekday, Email, mMail, s.Postcode, Prefecture, City, Town, AddressLine1, AddressLine2 FROM Student s
LEFT JOIN Enrol en ON s.StudentID = en.StudentID
LEFT JOIN SchCourse sc ON en.SchCourseID = sc.SchCourseID
LEFT JOIN Course c ON sc.CourseID = c.CourseID
LEFT JOIN FEnrol fe ON c.CourseID = fe.CourseID
LEFT JOIN Address a ON s.Postcode = a.Postcode GROUP BY FirstName ORDER BY FirstName ASC
INNER JOIN KidsStudent k ON s.StudentID = k.StudentID
WHERE k.DateOfBirth = '0000-00-00' AND (k.DateOfBirth BETWEEN 2001-01-01 AND 1995-12-31)
GROUP BY FirstName ORDER BY FirstName ASC

[awjudd]

How can the same field have two values at one time?

 

WHERE k.DateOfBirth = '0000-00-00' AND (k.DateOfBirth BETWEEN '1995-12-31' AND '2001-01-01')

 

You are checking where it is empty (i.e. '0000-00-00) AND it is between 1995 AND 2001.

 

~juddster

[jd22]

0000-00-00 is default value. if there is no data insert then record has 0000-00-00

 

[awjudd]

But the value can't be '0000-00-00' AND between '1995-12-31' AND '2001-01-01'.  It could be '0000-00-00' OR between '1995-12-31' AND '2001-01-01'.

 

~juddster

[jd22]

Dear Juddster

The problem that is not grabbing data even without WHERE clause.

But somehow query return data with

SELECT FirstName, LastName, CourseName, LessonStart, Weekday, Email, mMail, s.Postcode, Prefecture, City, Town, AddressLine1, AddressLine2 FROM Student s

INNER JOIN KidsStudent k ON s.StudentID = k.StudentID  GROUP BY FirstName ORDER BY FirstName ASC

[awjudd]

Two reasons why I doubt that the query you are providing is the one that you are running and is returning data:

- Your Student table or your KidsStudent table contain a field called 'CourseName' and 'LessonStart' - unlikely because those are course level information (i.e. likely to be coming from c.Course in your previous query)

- You are GROUPing BY 1 field and then applying no aggregation on the rest of them - this should cause an error because it doesn't make any sense

 

This query should return information:

SELECT FirstName, LastName
FROM Student s
JOIN KidsStudent k ON s.StudentID = k.StudentID
GROUP BY FirstName, LastName

 

~juddster

[jd22]

Two reasons why I doubt that the query you are providing is the one that you are running and is returning data:

- Your Student table or your KidsStudent table contain a field called 'CourseName' and 'LessonStart' - unlikely because those are course level information (i.e. likely to be coming from c.Course in your previous query)

- You are GROUPing BY 1 field and then applying no aggregation on the rest of them - this should cause an error because it doesn't make any sense

 

This query should return information:

SELECT FirstName, LastName
FROM Student s
JOIN KidsStudent k ON s.StudentID = k.StudentID
GROUP BY FirstName, LastName

 

~juddster

 

Your query returned 192 records which is correct data.

But why it is not return any records in my query.

Is there any limit to joining tables? In my case i am trying to merge 7 tables.

 

I am testing many different possibilities and query works if i remove any of table (joining 6 tables) but adding one more (joining 7 tables) fail.

So far i found that kidsstudent table doesn't cause this mess but number of tables.

 

Is there any limitation for INNER JOIN ??

[awjudd]

There is no limitation on the number of tables which can be joined nor any limitations for INNER JOIN.

 

If anything, something else in your query is wrong which is why for starters I pointed out the k.Birthday = '0000-00-00' AND k.Birthday BETWEEN ...

 

When you add KidsStudent to the query does the query fail or just return no results?  Depending on which it is it points to two completely different issues.  That said, with my previous query we have proven that the relationship between Student and KidsStudent exists and will cause the data set being returned to be reduced but not empty (i.e. the JOIN isn't failing).

 

~juddster

[jd22]

There is no limitation on the number of tables which can be joined nor any limitations for INNER JOIN.

 

If anything, something else in your query is wrong which is why for starters I pointed out the k.Birthday = '0000-00-00' AND k.Birthday BETWEEN ...

 

When you add KidsStudent to the query does the query fail or just return no results?  Depending on which it is it points to two completely different issues.  That said, with my previous query we have proven that the relationship between Student and KidsStudent exists and will cause the data set being returned to be reduced but not empty (i.e. the JOIN isn't failing).

 

~juddster

When i add KidsStudent  then query return no result. I tried die(mysql_error()) for test purpose that i want to see if is there any problem with query and i got white blank page .

But i know there 192 kid students in KidsStudent which is linked to Student table.