Join with inner SELECT sub-query

Hello,

I have the following raw SQL:

SELECT e.employee_no, e.birth_year 
FROM employees e
WHERE e.birth_year < '2007';

And I want to do a join with a sub query inside:

SELECT e.employee_no, e.birth_year, p.product_type
FROM employees e
INNER JOIN (
    SELECT employee_no, product_type
    FROM (
        SELECT id, employee_no, product_type,
               ROW_NUMBER() OVER (PARTITION BY employee_no ORDER BY id DESC) AS row_num
        FROM products
    ) AS row
    WHERE row_num = 1 AND product_type= '4'
) p
ON e.employee_no = p.employee_no
WHERE e.birth_year = e.birth_year < '2007';

The problem is, I did the first query using query builder:

DB::table('employees e')->where('e.birth_year', '<', '2007')->get();

How can I add the join part to the query builder query, if possible, i.e. this part:

INNER JOIN (
    SELECT employee_no, product_type
    FROM (
        SELECT id, employee_no, product_type,
               ROW_NUMBER() OVER (PARTITION BY employee_no ORDER BY id DESC) AS row_num
        FROM products
    ) AS row
    WHERE row_num = 1 AND product_type= '4'
) p
ON e.employee_no = p.employee_no

Thanks

jlrdw

1 year ago

Level 75

A suggestion only, if this query works I'd use as is and not bother with builder.

I have done queries like this, sample data only for a previous reply:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1

Normally I use a little trial and error and a combination of examples in the documentation to work out the query.

But usually things like yours I use regular mysql and pdo queries. And use the lengthaware paginator if needing to paginate.

Also there are times where I query a query.

Please or to participate in this conversation.