Design problem: fetching more data for some of the records I retrieve in an sql query.

Question

Say I have a query that fetches [type][show_name].
For all [type]==5 records, I need to join them with another table.

What would be the best approach for that:

1. Join for all records between the two tables (looks bad).
2. Run the query and then run again on the result set, fetch all the IDs and do a IN query on the table I need to join with.
3. Fetch the missing data only after what I have is presented (using the same IN query), maybe with an AJAX approach.
4. Something I haven't thought of.

Important thing is, I want the user to see at least some of the data as fast as possible, but I also want the code to be straight forward, without too much spaghetti.

Answer 1

You could do a left join and put the type=5 clause inside the join condition. This will return you null in the other table if the type isn't 5.

select
    a.type,
    a.show_name,
    b.whatever
from
    tableA a
    left outer join tableB b on
        a.id = b.id
        and a.type = 5

This works because it only joins the records with type=5. If you were to put a.type=5 inside the where clause, it would limit your result set to those where type=5.