1 Replies - 1955 Views - Last Post: 18 November 2013 - 01:49 AM Rate Topic: -----

#1 Keylogger  Icon User is offline

  • D.I.C Regular

Reputation: 7
  • View blog
  • Posts: 344
  • Joined: 14-February 11

SQLite get "deeper" image

Posted 14 November 2013 - 09:04 AM

Hello,

I'll try not to confuse you.

Table products 
  -> product_id
  -> name
  -> image

Table products_to_category
  -> product_id
  -> category_id

Table category
  -> category_id
  -> parent_id

Table category_description
  -> category_id
  -> name


As you may already know, I can have multiple parents/categories. Is the following example:

Category Parent Computer
         Category Parent Hardware
                  -> Category Motherboard
                  -> Category Parent CPU
                              -> Category INTEL
                              -> Category AMD
                        
         Category Parent Software
                  -> Category Microsoft
                  -> Category Apple

Category Parent Accessories
         -> Category Headsets
         -> Category Mouses


...And so on.

What I want is by giving the FIRST Category Parent ID (in this case, "Computer" OR "Accessories"), for example '600', I would like to retrieve only ONE image from ANY category of that Parent Category.

This is what I've tried so far:

SELECT prod.image
FROM product AS prod
JOIN product_to_category AS prod_to_category ON prod_to_category.product_id = prod.product_id
JOIN category AS cat ON cat.category_id = prod_to_category.category_id
WHERE cat.parent_id = 600 AND prod.image NOT NULL
ORDER BY RANDOM() LIMIT 1


If I change the "cat_parent_id = 600" to "cat_parent_id = 720" (which is the Category Parent "Hardware"), it works fine.
With the current code, I get no output.

How can I achieve what I want?

Is This A Good Question/Topic? 0
  • +

Replies To: SQLite get "deeper" image

#2 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3730
  • View blog
  • Posts: 6,017
  • Joined: 08-June 10

Re: SQLite get "deeper" image

Posted 18 November 2013 - 01:49 AM

You can't achieve this with SQLite queries alone. What you need is to is do a recursive search, which is not something SQLite supports. Other databases, like MSSQL and Oracle, can achieve this, but the feature is fairly advanced; far beyond the level of SQLite. Even MySQL doesn't support it.

If you are interested in how this works in other databases, e_i_pi wrote a great tutorial on the subject:
- Developing robust hierarchical data in MSSQL

As it stands, to do this with SQLite, you'll need to leverage whatever programming language you are using with SQLite and do the search from there. Have it recursively load all the categories, then execute a query that finds all products for those categories, and return a random one.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1