Querying Multiple Subtypes

[SaranacLake]

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 September 12, 2020 by SaranacLake

[SaranacLake]

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 September 12, 2020 by SaranacLake

[Barand]

Perhaps this answer I gave you a year ago might help

 

[SaranacLake]

6 hours ago, Barand said:

Perhaps this answer I gave you a year ago might help

You proposed EAV which is a whole other beast.  It's also a different design that I'm not suing.

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

 

 

 

[Barand]

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.

[SaranacLake]

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??