Help with nested join


#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:


#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


#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

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                       

#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 )


#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


#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

#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


#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.