How can I write this as a JOIN?

Databases
1

How can I write this as a JOIN?:

SELECT id, item_id, vendor_id, datetime2 FROM nconnectz AS a

WHERE datetime2 = (SELECT MAX(datetime2)

FROM nconnectz AS b

WHERE a.item_id = b.item_id

AND a.vendor_id = b.vendor_id

AND item_id IN (' . $in . ') AND vendor_id IN (' . $in2 . ')

Something like this, (but not this because it doesn’t work):

SELECT id
				, a.item_id
				, a.vendor_id
				, a.datetime2 
			FROM nconnectz AS a
			JOIN (SELECT b.item_id
					, b.vendor_id
					, MAX(datetime2) AS maxdatetime
				FROM nconnectz) AS b
			ON a.item_id = b.item_id
			AND a.vendor_id = b.vendor_id
			AND a.datetime2 = b.maxdatetime
			WHERE item_id IN (' . $in . ') AND vendor_id IN (' . $in2 . ')
2 
SELECT nconnectz.id
     , nconnectz.item_id
     , nconnectz.vendor_id
     , nconnectz.datetime2 
  FROM ( SELECT item_id   
              , vendor_id 
              , MAX(datetime2) AS latest 
           FROM nconnectz 
          WHERE item_id IN (' . $in . ') 
            AND vendor_id IN (' . $in2 . ')
         GROUP
             BY item_id   
              , vendor_id ) AS subset
INNER
  JOIN nconnectz
    ON nconnectz.item_id   = subset.item_id    
   AND nconnectz.vendor_id = subset.vendor_id               
   AND nconnectz.datetime2 = subset.latest
1 Like
3

r937, your code is very innovative! I don’t remember seeing anything that resembles your work on stack overflow and similar websites. I obviously wasn’t thinking that way.

Is the first select called a Derived Table?

EDIT:
https://dev.mysql.com/doc/refman/8.0/en/derived-tables.html

Yes. Derived Table.

What prompted you to use that approach?

4

subquery in the FROM clause is a pretty routine thing, sir

1 Like
5

For me too now!

Please know that I am not trying to flatter you.

I’m just deeply grateful for your help and new ways to think about sql and code in general.

niche

6

i just realized this didn’t work because i forgot the GROUP BY clause in the subquery

7

Do you mean I was closer than I thought (needed to add GROUP BY)?

8

My nconnectz table also has a price column.

What should my thinking be to select the latest datetime with the lowest price?

The first query is r937’s from a previous post and correctly selects 7 rows.
The second query correctly selects the lowest prices from based on the 1st query.

How do I join these two queries?

NOTE: Only difference between the 1st and 2nd query is the first and last line of the 2nd query.

QUERY 1:

SELECT id
, nconnectz.item_id
, nconnectz.vendor_id
, nconnectz.store
, nconnectz.datetime2
, nconnectz.itemandsize
, 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 (7)
AND vendor_id IN (5,2)
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
ORDER BY item_id ASC , vendor_id ASC, pkg_quantity ASC

QUERY 2:

SELECT MIN(price) AS minprice FROM (
SELECT id
, nconnectz.item_id
, nconnectz.vendor_id
, nconnectz.store
, nconnectz.datetime2
, nconnectz.itemandsize
, 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 (7)
AND vendor_id IN (5,2)
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
ORDER BY item_id ASC , vendor_id ASC, pkg_quantity ASC
) AS subset3 GROUP BY vendor_id

9

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