[Solved] Join query on size variations

Creating tables and queries

[Solved] Join query on size variations

Postby cartoonjazz » Thu May 11, 2017 7:18 pm

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
cartoonjazz
 
Posts: 36
Joined: Sun Sep 04, 2016 9:40 pm

Re: Join query on size variations

Postby UnklDonald418 » Thu May 11, 2017 11:32 pm

Try this
Code: Select all   Expand viewCollapse view
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.6 & LibreOffice 6.1.1.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 979
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Join query on size variations

Postby eremmel » Fri May 12, 2017 9:53 am

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   Expand viewCollapse view
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.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1016
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Join query on size variations

Postby cartoonjazz » Fri May 12, 2017 4:10 pm

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=08495635285526457187
if you can help out much appreciated
openoffice 4.1.2 windows 10
cartoonjazz
 
Posts: 36
Joined: Sun Sep 04, 2016 9:40 pm

Re: Join query on size variations

Postby eremmel » Fri May 12, 2017 4:51 pm

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
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1016
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Join query on size variations

Postby cartoonjazz » Fri May 12, 2017 5:03 pm

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
cartoonjazz
 
Posts: 36
Joined: Sun Sep 04, 2016 9:40 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 6 guests