SQLite parse query results from autoit side or SQL

[frank10]

Hi, I tried to make my first SQLite DB to query from Autoit.

Well, it seems a bit complex... (so, please be patient while I'm explaining it 😊 )

I try to explain the logic:

It's a DB of books, each with their bookID primarily consisting in a couple of data: IDbookCode + marketplace
Each book has title, subtitle, author, variable rankPositions and numberReviews, price, royalties etc in different dates and other info

Different books can have the same title, but not the same subtitle. (i.e my book + all competitors can have the same title, not subtit)
The most numerous data should be filled in the rank column, as it's always changing and every day could be parsed and inserted for each book...

Each book of mine could have a mainMarket but also can be marketed in other minor markets, so on Book table, I could have:

BookID, ASIN, marketID

1, IDcode1, com

2, IDcode1, co.uk

So, the only IDcode is not sufficient to identify a book. (i.e. in the testDB, bookID 22 & 32, and bookID 16 & 33 are the same books but on different markets)

On Book table I have also a isCompet column where I indicate if the book is my book (=0) or one of the competitor (=BookID) of the book which competes)

I collect info on each book I want to monitor on the table BookData where I store in different date, variations on price, rank, review and so on

I created also Price table to put:

price, royaltiesBN, royaltiesCol (eventually), ACOS for ads and so on (Price as integer*100, so you must /100 for the final value)

I used FK to connect various tables, but for example when I don't have a price for a book, I put a null on the FK on BookData (because on that monitor date I have anyway other changed data such as rank or reviews etc)

I used this model:

TableName

tableNameID for the PK

myForeignKeyID on both TableName and TableNameFK

(I use SQLite expert to test it and it seems it does not allow to change name for a FK on the 2 tables to differentiate...)

 

I attach an example of Test DB.

 

So, let's make a query example:

What I want is having ALL MY books I inserted with or without a priceID, but keeping only the last date price for those that have price monitored.

I started with:

select b.bookID, d.actualDate, b.ASIN, m.market, t.title, a.author, bt.bookType, p.price, p.royalBN, r.rank, re.review, tr.totalRating
FROM Book b    
left JOIN  BookData bd on bd.bookID = b.bookID
left JOIN  Date d on d.dateID = bd.dateID
left JOIN  Price p on p.priceID = bd.priceID
left JOIN  Review re on re.reviewID = bd.reviewID
left JOIN  TotalRating tr on tr.totalRatingID = bd.totalRatingID
left JOIN  Rank r on r.rankID = bd.rankID
left JOIN  Market m on b.marketID = m.marketID
left JOIN  Author a on a.authorID = b.authorID
left JOIN  Title t on b.titleID = t.titleID
left JOIN  Subtitle st on b.subtitleID = st.subtitleID
left JOIN  BookType bt on b.bookTypeID = bt.bookTypeID
where isCompet = 0
order by b.bookID

But I got a lot of duplicates...

Trying to get only the more recent date insertion for prices, I tried:

select b.bookID, d.actualDate, b.ASIN, m.market, t.title, a.author, bt.bookType, p.price, p.royalBN, r.rank, re.review, tr.totalRating
FROM Book b    
left JOIN  BookData bd on bd.bookID = b.bookID
left JOIN  Date d on d.dateID = bd.dateID
left JOIN  Price p on p.priceID = bd.priceID
left JOIN  Review re on re.reviewID = bd.reviewID
left JOIN  TotalRating tr on tr.totalRatingID = bd.totalRatingID
left JOIN  Rank r on r.rankID = bd.rankID
left JOIN  Market m on b.marketID = m.marketID
left JOIN  Author a on a.authorID = b.authorID
left JOIN  Title t on b.titleID = t.titleID
left JOIN  Subtitle st on b.subtitleID = st.subtitleID
left JOIN  BookType bt on b.bookTypeID = bt.bookTypeID
where (bd.priceID,bd.dateID) IN      
( SELECT bd.priceID, MAX(bd.dateID)
  FROM BookData bd
  GROUP BY bd.priceID
) 
and isCompet = 0
order by b.bookID

And it works good BUT only for those books that I inserted a price. For some reason, I did not got the price for some books, so they have a FK  priceID NULL.

And those does not display anymore... So I lost a lot of books in the final result...

I could get the first redundant result and parse it with Autoit, but maybe it should be better to get all already ok from SQL query...?

 

What do you think of the DB structure?

How would you query this example?

TIA

 

testDB1.sq3

Edited October 20, 2022 by frank10