[Solved] Join query on size variations

Creating tables and queries
Post Reply
cartoonjazz
Posts: 54
Joined: Sun Sep 04, 2016 9:40 pm

[Solved] Join query on size variations

Post by cartoonjazz »

I appreciate any help that can be offered.
I have table 1 that shows parent skus, prices and item type. Table 2 has the parent items and the size variations. I would like to populate table 2 with the prices from table 1 applied to both the parent and sizes.

The sizes for all items is displayed after a dash so it should join based on everything before the first dash. In addition all items that are identified as "shirts" in the "type" column are to be joined by the first 4 characters of the sku in table 2 so all skus beginning with 1099 should have that price including 109999, 109983 and 109965 as well as their sizes such as 109999-Size-M,,,etc.

table 1
Image

table 2
Image

result should be
Image

thanks again
Last edited by cartoonjazz on Fri May 12, 2017 5:04 pm, edited 1 time in total.
openoffice 4.1.2 windows 10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Join query on size variations

Post by UnklDonald418 »

Try this

Code: Select all

UPDATE "Table2" SET 
"Table2"."price" = SELECT  "Table1"."price"   
FROM "Table1" 
WHERE "Table1"."sku" LIKE LEFT("Table2"."sku",LENGTH("Table1"."sku"))
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Join query on size variations

Post by eremmel »

The basis is good, but I think this will fail when you have a SKU with e.g. '10' (no 'shirt'). The idea is to take the '-' in the comparison as well in that case.
Also not sure why you take a LIKE and not an = as comparison.
The WHERE might look like:

Code: Select all

WHERE  "Table1"."sku" = LEFT("Table2"."sku",LENGTH("Table1"."sku")) AND "Table1"."type" = 'shirt' 
      OR "Table1"."sku" = "Table2"."sku" 
      OR "Table1"."sku" || '-' = LEFT("Table2"."sku",LENGTH("Table1"."sku")+1)
Note that the code above changes the requirement n addition all items that are identified as "shirts" in the "type" column are to be joined by the first 4 characters of the sku in table 2 into all 'shirt' types are joined based on a left match of table2.sku with table1.sku. When all table1.skus of 'shirts' are length 4 this is the same rule.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
cartoonjazz
Posts: 54
Joined: Sun Sep 04, 2016 9:40 pm

Re: Join query on size variations

Post by cartoonjazz »

I am running base in HSQL database engine, I tried the recommend of
UPDATE "Table2" SET
"Table2"."price" = SELECT "Table1"."price"
FROM "Table1"
WHERE "Table1"."sku" LIKE LEFT("Table2"."sku",LENGTH("Table1"."sku"))WHERE "Table1"."sku" = LEFT("Table2"."sku",LENGTH("Table1"."sku")) AND "Table1"."type" = 'shirt'
OR "Table1"."sku" = "Table2"."sku"
OR "Table1"."sku" || '-' = LEFT("Table2"."sku",LENGTH("Table1"."sku")+1)

but received an error "SQL Status: HY000 Error code: 1000 syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE"

This query doesn't have to update the existing table can just be a select /join

the database is at http://s000.tinyupload.com/?file_id=084 ... 5526457187
if you can help out much appreciated
openoffice 4.1.2 windows 10
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Join query on size variations

Post by eremmel »

Note that the query you posted contains two times a WHERE-clause You should use either of the suggested ones.
You can't run an UPDATE statement as a query statement. You have to copy/past it into the SQL dialog: Menu -> tools -> 'SQL....'
You can test your SQL statement better when you use 'Direct SQL' mode for your query, there is a 'SQL' button to enable this.
You can test your update statement by making it an SELECT statement first (and examine the wanted result) use:
SELECT "Table2"."sku", "Table2"."price", "Table1"."sku", "Table1"."price"
FROM "Table1", "Table2"
put-here-one-of-the-two-WHERE-clauses
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
cartoonjazz
Posts: 54
Joined: Sun Sep 04, 2016 9:40 pm

Re: Join query on size variations

Post by cartoonjazz »

Thanks so much works great here is the working sql

SELECT "Table1"."sku", "Table1"."price", "Table2"."sku" FROM "Table1", "Table2"
WHERE "Table1"."sku" = LEFT("Table2"."sku",LENGTH("Table1"."sku")) AND "Table1"."type" = 'shirt'
OR "Table1"."sku" = "Table2"."sku"
OR "Table1"."sku" || '-' = LEFT("Table2"."sku",LENGTH("Table1"."sku")+1)
openoffice 4.1.2 windows 10
Post Reply