mysql query
mysql query
mysql query
mysql query
mysql query
mysql query mysql query mysql query mysql query mysql query mysql query mysql query mysql query
mysql query mysql query
mysql query
Go Back  Xtreme Visual Basic Talk > > > mysql query


Reply
 
Thread Tools Display Modes
  #1  
Old 02-23-2013, 05:10 AM
Ktech22 Ktech22 is offline
Newcomer
 
Join Date: Jun 2012
Posts: 20
Default mysql query


Hi I need help with this sql query I have three tables learner,homework and result. Now If the learner is assigned an homework which will due after current date such as two weeks time. However if the learner has completed the homework then there is a row inserted in result with that particular score using a compound key of student id and homework id. Now I want a query which finds the homework details which are due in the future which the learner hasn't completed.
thanks
ktech22
Reply With Quote
  #2  
Old 02-25-2013, 09:11 AM
DrPunk's Avatar
DrPunkmysql query DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

The key to this is finding rows in a table (Homework) based on it not having rows in another table (Results).

Commonly JOINS on tables are INNER JOINS, that is they return rows where both tables have data based on the JOIN. Such as, if you were to INNER JOIN Homework and Results you'd only get the Homework rows that have corresponding Results rows.

But there are also OUTER JOINS which will return all the rows on one table regardless of whether the second table has rows based on the JOIN. The table returning all rows depends on the direction of the JOIN. Left or Right.

So, knowing that a OUTER JOIN will return all rows for one table regardless of the other table having rows to join, if you want to know all the rows from the first table that don't have rows in the second table you can do an OUTER JOIN and then WHERE on the second table's fields being NULL (because those rows without data to join on will have to be NULL).

So, to find all the Homeworks that don't have Results you could query...
Code:
SELECT *
FROM tbHomework
    LEFT OUTER JOIN tbResults ON tbHomework.ID = tbResults.HomeworkID
WHERE tbResults.HomeworkID IS NULL
Hopefully that gives you somewhere to start from. It shouldn't be difficult to add Learner to that. If you need further advice then please ask.
__________________
There are no computers in heaven!
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
mysql query
mysql query
mysql query mysql query
mysql query
mysql query
mysql query mysql query mysql query mysql query mysql query mysql query mysql query
mysql query
mysql query
 
mysql query
mysql query
 
-->