Help with nested join

Databases
1

I’d like to MAX the time_stamp coming from the z alias.

Am correct in thinking that I need to apply the pattern in the subset alias to the z alias?

here’s my code so far:

SELECT id
  , z.route_order
  , z.time_stamp
  , nconnectz.datetime2
  , nconnectz.item_id 
  , nconnectz.vendor_id
  , nconnectz.store
  , nconnectz.sub_category
  , nconnectz.itemandsize
  , nconnectz.item
  , nconnectz.pkg_quantity
  , nconnectz.pkg_price
  , nconnectz.price
  FROM ( 
    SELECT item_id 
    , pkg_quantity	
    , vendor_id 
    , MAX(datetime2) AS latest 
    FROM nconnectz 
    WHERE item_id IN (6,7) 
      AND vendor_id IN (2,5)
    GROUP BY item_id , vendor_id, pkg_quantity
  ) AS subset 
  INNER JOIN nconnectz 
    ON nconnectz.item_id   = subset.item_id    
      AND nconnectz.vendor_id = subset.vendor_id               
      AND nconnectz.datetime2 = subset.latest
	  AND  nconnectz.pkg_quantity = subset.pkg_quantity 
  INNER JOIN (SELECT route_order, item, time_stamp FROM route_order WHERE user_id = 31) AS z 
    ON z.item = nconnectz.item
	  ORDER BY item_id ASC , vendor_id ASC, price ASC, pkg_price ASC
2

yup :slight_smile:

1 Like
3

I think I followed the pattern, but no joy.

What should I rethink?

SELECT id
  , z.route_order
  , z.time_stamp
  , nconnectz.datetime2
  , nconnectz.item_id 
  , nconnectz.vendor_id
  , nconnectz.store
  , nconnectz.sub_category
  , nconnectz.itemandsize
  , nconnectz.item
  , nconnectz.pkg_quantity
  , nconnectz.pkg_price
  , nconnectz.price
  FROM ( 
    SELECT item_id 
    , pkg_quantity	
    , vendor_id 
    , MAX(datetime2) AS latest 
    FROM nconnectz 
    WHERE item_id IN (6,7) 
      AND vendor_id IN (2,5)
    GROUP BY item_id , vendor_id, pkg_quantity
  ) AS subset 
  INNER JOIN nconnectz 
    ON nconnectz.item_id   = subset.item_id    
      AND nconnectz.vendor_id = subset.vendor_id               
      AND nconnectz.datetime2 = subset.latest
	  AND  nconnectz.pkg_quantity = subset.pkg_quantity 
  INNER JOIN (SELECT route_order, item, time_stamp 
	FROM (
		SELECT route_order, item, MAX(time_stamp) AS latest_ts FROM route_order WHERE user_id = 31
	) AS z2 
  INNER JOIN route_order AS z 
    ON z.item = nconnectz.item
	  AND z.time_stamp = z2.latest_ts
  ORDER BY item_id ASC , vendor_id ASC, price ASC, pkg_price ASC
4

rethink the GROUP BY clause in the z2 subquery

1 Like
5

Ah yes, but still no joy.

Also, have an open paren that I don’t know how to resolve. Deleting it doesn’t help.

Current code:

SELECT id
  , z.route_order
  , z.time_stamp
  , nconnectz.datetime2
  , nconnectz.item_id
  , nconnectz.vendor_id
  , nconnectz.store
  , nconnectz.sub_category
  , nconnectz.itemandsize
  , nconnectz.item
  , nconnectz.pkg_quantity
  , nconnectz.pkg_price
  , nconnectz.price
FROM (
  SELECT item_id
  , pkg_quantity
  , vendor_id
  , MAX(datetime2) AS latest
  FROM nconnectz
  WHERE item_id IN (6,7)
  AND vendor_id IN (2,5)
  GROUP BY item_id , vendor_id, pkg_quantity
) AS subset
INNER JOIN nconnectz
  ON nconnectz.item_id = subset.item_id
    AND nconnectz.vendor_id = subset.vendor_id
    AND nconnectz.datetime2 = subset.latest
    AND nconnectz.pkg_quantity = subset.pkg_quantity
INNER JOIN (SELECT route_order, item, time_stamp
FROM (
SELECT route_order, item, MAX(time_stamp) AS latest_ts FROM route_order WHERE user_id = 31
GROUP BY route_order, item) AS z2
INNER JOIN route_order AS z
ON z.item = nconnectz.item
AND z.time_stamp = z2.latest_ts
ORDER BY item_id ASC , vendor_id ASC, price ASC, pkg_price ASC
6

indent your code, man

it’ll jump right out at you

[code]
SELECT id
, z.route_order
, z.time_stamp
, nconnectz.datetime2
, nconnectz.item_id
, nconnectz.vendor_id
, nconnectz.store
, nconnectz.sub_category
, nconnectz.itemandsize
, nconnectz.item
, nconnectz.pkg_quantity
, nconnectz.pkg_price
, nconnectz.price
FROM ( SELECT item_id
, pkg_quantity
, vendor_id
, MAX(datetime2) AS latest
FROM nconnectz
WHERE item_id IN (6,7)
AND vendor_id IN (2,5)
GROUP
BY item_id
, vendor_id
, pkg_quantity ) AS subset
INNER
JOIN nconnectz
ON nconnectz.item_id = subset.item_id
AND nconnectz.vendor_id = subset.vendor_id
AND nconnectz.datetime2 = subset.latest
AND nconnectz.pkg_quantity = subset.pkg_quantity
INNER
JOIN ( SELECT route_order
, item
, time_stamp
FROM ( SELECT route_order
, item
, MAX(time_stamp) AS latest_ts
FROM route_order
WHERE user_id = 31
GROUP
BY route_order
, item ) AS z2
INNER

  -- uh oh                       
                   [/code]
1 Like
7

I think you’re saying that I almost have it, but I don’t see it.

I don’t know where to close the paren, and how to assign the z alias to the table called route_order.

8

hint: It’s somewhere close to where @r937 put

-- uh oh

Count your parenthesis very close. For every ( you should have a )

1 Like
15

Please confirm my thinking.

I want to add a second join at the end of the first join.

So, my job is to nest a subquery in a second join and join the joins.

Right?

16

yeah, something like that

here, i’ll give you a template –

SELECT ... FROM table1 INNER JOIN table2 /* SEE NOTE 1 */ ON table2.something = table1.something INNER JOIN table3 ON table3.someotherthing = /* SEE NOTE 2 */ INNER JOIN table4 ON table4.somethingelse = /* etc. */

note 1 – each table (including table 1) can actually be a derived table, i.e. a subquery in parentheses given a table alias

note 2 – always join on a column of a previously mentioned table, so in this example, table 3 could be joined based on column(s) from table 2 or table 1 or both

1 Like
18

I think this follows the template though I get this error: Unknown column ‘z.latest_ts’ in ‘field list’

Where’s my variation from the template?

(also found the trick to make my indent stick)

SELECT id
	, z.route_order
	, z.latest_ts
	, nconnectz.datetime2
	, nconnectz.item_id
	, nconnectz.vendor_id
	, nconnectz.store
	, nconnectz.sub_category
	, nconnectz.itemandsize
	, nconnectz.item
	, nconnectz.pkg_quantity
	, nconnectz.pkg_price
	, nconnectz.price
	FROM (
			SELECT item_id
				, pkg_quantity
				, vendor_id
				, MAX(datetime2) AS latest
			FROM nconnectz
			WHERE item_id IN (6,7)
			AND vendor_id IN (2,5)
			GROUP BY item_id , vendor_id, pkg_quantity
		) AS subset
INNER 
	JOIN nconnectz
		ON nconnectz.item_id = subset.item_id
			AND nconnectz.vendor_id = subset.vendor_id
			AND nconnectz.datetime2 = subset.latest
			AND nconnectz.pkg_quantity = subset.pkg_quantity
INNER 
	JOIN (
		SELECT route_order
			, item
			, time_stamp
			FROM ( 
				SELECT route_order
				, item
				, MAX(time_stamp) AS latest_ts
				FROM route_order
				WHERE user_id = 31
				GROUP BY route_order
					, item
			) AS z2
		INNER 
			JOIN
				route_order 
			ON
				route_order.time_stamp = z2.latest_ts
		GROUP BY
			route_order.route_order, z.item
	) AS z
ON
	z.item = nconnectz.item
19

Worked on the aliases and now it works!

Thanks to r937 and DaveMaxwell.

Successful code:

SELECT id
	, z3.route_order
	, z3.time_stamp
	, nconnectz.datetime2
	, nconnectz.item_id
	, nconnectz.vendor_id
	, nconnectz.store
	, nconnectz.sub_category
	, nconnectz.itemandsize
	, nconnectz.item
	, nconnectz.pkg_quantity
	, nconnectz.pkg_price
	, nconnectz.price
	FROM (
			SELECT item_id
				, pkg_quantity
				, vendor_id
				, MAX(datetime2) AS latest
			FROM nconnectz
			WHERE item_id IN (6,7)
			AND vendor_id IN (2,5)
			GROUP BY item_id , vendor_id, pkg_quantity
		) AS subset
INNER 
	JOIN nconnectz
		ON nconnectz.item_id = subset.item_id
			AND nconnectz.vendor_id = subset.vendor_id
			AND nconnectz.datetime2 = subset.latest
			AND nconnectz.pkg_quantity = subset.pkg_quantity
INNER 
	JOIN (
		SELECT z.route_order
			, z.item
			, z.time_stamp
			FROM ( 
				SELECT route_order
				, item
				, MAX(time_stamp) AS latest_ts
				FROM route_order
				WHERE user_id = 31
				GROUP BY route_order
					, item
			) AS z2
		INNER 
			JOIN
				route_order AS z
			ON
				z.time_stamp = z2.latest_ts
		GROUP BY
			z.route_order, z.item
	) AS z3 
ON
	z3.item = nconnectz.item
2 Likes
20

i think your join is incomplete here

      ( 
				SELECT route_order
				, item
				, MAX(time_stamp) AS latest_ts
				FROM route_order
				WHERE user_id = 31
				GROUP BY route_order
					, item
			) AS z2
		INNER 
			JOIN
				route_order AS z
			ON
				z.time_stamp = z2.latest_ts

it will run, but it joins route_order to only the latest timestamp, ragardless of whether it’s for the correct route order or item

note the GROUP BY – there are multiple values for latest_ts

if all you want is only one latest_ts overall, you don’t really need the subquery

1 Like
21

Great catch! I was so thrilled that it worked, that I missed that.

I wonder how long I would’ve missed that? After all, that was the point of selecting route order and item as additional selects.

Top-of-the-mind-reminder: The possibility that it works, but not the way I think it does.

New subquery:

(
SELECT z.route_order
			, z.item
			, z.time_stamp
			FROM ( 
				SELECT route_order
				, item
				, MAX(time_stamp) AS latest_ts
				FROM route_order
				WHERE user_id = 31
				GROUP BY route_order
					, item
			) AS z2
		INNER 
			JOIN
				route_order AS z
			ON z.time_stamp = z2.latest_ts
#additional conditions              
			AND z.item = z2.item
			AND z.route_order = z2.route_order
#additional conditions
				
		GROUP BY
			z.route_order, z.item
)
22

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.