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
table 2
result should be
thanks again
[Solved] Join query on size variations
-
- Posts: 54
- Joined: Sun Sep 04, 2016 9:40 pm
[Solved] Join query on size variations
Last edited by cartoonjazz on Fri May 12, 2017 5:04 pm, edited 1 time in total.
openoffice 4.1.2 windows 10
-
- Volunteer
- Posts: 1544
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Join query on size variations
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
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: Join query on size variations
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:
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.
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)
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
-
- Posts: 54
- Joined: Sun Sep 04, 2016 9:40 pm
Re: Join query on size variations
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
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
Re: Join query on size variations
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
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)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
-
- Posts: 54
- Joined: Sun Sep 04, 2016 9:40 pm
Re: Join query on size variations
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)
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