Jump to content

Querying Multiple Subtypes


SaranacLake

Recommended Posts

My database has parent Product table and multiple sub-type tables like Books, Tshirts, Gear, etc.

I need a way to display everything in the Product catalog including details only found in the sub-type tables.

How do I do that?

When I tried multiple INNER JOINS my query didn't return any records.

Maybe I need to use a UNION?

 

Edited by SaranacLake
Link to comment
Share on other sites

Here is a little more of what (I think) I'm trying to do...

	PRODUCT (parent)
	- id
	- product_type_code
	- sku
	

 

	BOOK (subtype)
	- id
	- product_type_code
	- title
	- thumbnail
	

 

	SHIRT (subtype)
	- id
	- product_type_code
	- name
	- thumbnail
	

 

I am trying to build a query that can be used to display all items in a person's shopping cart.

Because I have disparate subtypes, some things won't match up - which is why i decided to create subtypes in the first place - but things like a product's name, description, and thumbnail should be universal for any subtypes.

 

My cart looks like this...

	SHOPPING_CART
	- id
	- member_id
	- product_id
	- cart_price
	- quantity
	

 

But in the shopping cart I display to the user, I also need some fields from the subtype tables.  (For example, a BOOK has a "title" whereas a SHIRT has a "name".

 

So how can I create a query so I can get results like this...

	- id
	- product_type_code
	- sku
	- book_title OR shirt_name
	- book_description OR shirt_description
    - thumbnail
	- price
	- quantity
	

 

 

Hopefully that makes sense!

 

Edited by SaranacLake
Link to comment
Share on other sites

2 hours ago, SaranacLake said:

You proposed EAV which is a whole other beast. 

.. or JSON as an alternative was also proposed. Both are suitable for products with different attribute sets.

 

2 hours ago, SaranacLake said:

Is a UNION the way to go with my current design?

Possibly - that's usual bandaid to resort to when you've spread data over several tables instead of one. Try it and see. Experimentation is a wonderful technique.

Link to comment
Share on other sites

In one subtype I have a field called "title" and in another substype I have a field called "name".

When I use a UNION, I am able to get them to appear in the same column, but it seems that the column name adopted is just the first query

	SELECT p.id, p.sku, mp.product_id, mp.name
	FROM product AS p
	INNER JOIN membership_plan AS mp
	on p.id = mp.product_id
	UNION
	SELECT p.id, p.sku, bk.product_id, bk.title
	FROM product AS p
	INNER JOIN book AS bk
	on p.id = bk.product_id
	

 

How can I "shift" things so my final query shows the columns:

id, sku, name, title

where the first table would fill out "name" and have blank "title" rows, and the second query would have blank "name" rows but filled out "title" rows??

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.