0

I have 2 tables named projects and tracklines Every project can have multiple tracklines.

I have an SQL statement that will loop through my projects, but I want to be able to JOIN the trackline table with only 1 row. What I want to do is get all tracklines that are part of a project, order them based on more than one field and do a LIMIT 1 on it so that I have one trackline which I'm joining with my project.

This is what I have so far:

SELECT Project.* FROM `project` AS `Project`
LEFT JOIN (
    SELECT *
    FROM `trackline`
    WHERE `trk_deleted` = '0'
    ORDER BY `trk_state`, `trk_status`
    LIMIT 1
) t ON t.`trk_project` = `prj_id`
ORDER BY `prj_name`

The problem is that I am not getting the trk_state and trk_status values in my outer query.

I was thinking that my subquery could have WHERE trk_project = prd_id but I am not sure how to get that to happen.

Marc B
  • 356,200
  • 43
  • 426
  • 500
Justin
  • 623
  • 1
  • 10
  • 24

1 Answers1

1

You need to pull values from that derived table as well:

SELECT Project.*, t.*
FROM ...

right now it's retrieving ONLY the fields from the Project table.

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • I forgot to mention that I have done that already even though my code didn't provide it. The main problem I'm having is that if I take away the "LEFT" from the join, no result set is returned meaning that no joins were being done. I figure I have something wrong with the logic I'm using to JOIN the 2 tables. – Justin Oct 28 '11 at 21:22
  • making it just a `JOIN` makes it a default `INNER JOIN`, where'd you get results only if there's a matching record on both sides of the query - which means your `ON` clause in the join isn't working right. – Marc B Oct 28 '11 at 21:25
  • Yea, I understand the difference between JOINS and LEFT JOINS. If I were to do a LEFT JOIN without using the subquery, I will get results. I'm guessing the method I'm using is what is causing the join not to work. – Justin Oct 28 '11 at 21:28
  • Is that limit necessary in the subquery? That causes it to return only a single record to be joined on. Most likely you don't have any records in the project table that match that one single record, so the inner join produces nothing. – Marc B Oct 28 '11 at 21:31
  • Is there some way I can bring the condition trk_project = prj_id into the subquery. I want the subquery to return a maximum of 1 result. The subquery doesn't like `prj_id` – Justin Oct 28 '11 at 21:45
  • Not really: http://stackoverflow.com/questions/530381/mysql-correlated-subquery-in-join-syntax – Marc B Oct 28 '11 at 21:48