Mysql left join with another table with passed variable

Question

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 · Accepted Answer · 2011-10-28 21:19:12Z

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.

answered Oct 28, 2011 at 21:19

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Justin Over a year ago

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.

Marc B Over a year ago

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.

Justin Over a year ago

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.

Marc B Over a year ago

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.

Justin Over a year ago

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

|

Collectives™ on Stack Overflow

Mysql left join with another table with passed variable

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related