Hi all I was wondering if somebody could help me out as I think I have frazzled my brain... I have the following SQL Tables ( and showing relevant columns below): courses - id - title - content courses_details - price - start_date - start_time - location Here is the code: <?php $bsql = "SELECT * FROM courses RIGHT JOIN courses_details ON courses_details.course_id = courses.id ORDER BY courses_details.price ASC, courses.id ASC"; // Run query $bq = $conn->query($bsql); ?> Code (markup): What I need to do is show DISTINCT courses, but show the cheapest price for each one. At the moment I can only either show just the distinct courses with no price or all of the courses with every price. Can this be done with just the one query or should I run an initial query to get the results in PHP and then another query on that returned data? Thanks in advance
You can use a simple join since there will always be records in both tables. Try something like this $sql = "select courses.id, courses.title, courses.content, min(courses_details.price) as price from courses, courses_details where courses.id = courses_details.course_id group by courses.id order by courses.title"; PHP: group by gives the distinct effect min() means you only see the cheapest price