Q: Joining 4 tables and assigning default rows I have the following 4 tables:
STORES:
id     |    name
------------------
1         Store 1
2         Store 2
3         Store 3
4         Store 4
5         Store 5
6         Store 6

ITEM_TYPES:
type   |  description
----------------------
*A           *All (Automatically all stores would have this item) 
*SP          *Store_Specfic (Only a specific store would have this item)
T            Toys
H            Home
P            Pet

STORES_ITEM_TYPES:
STORE_ID   |  ITEM_TYPE
------------------------
1                T
1                P
3                T
5                T
5                H
5                P

ITEMS:
ITEM_NAME           |       SPECIFIC_STORE_ID       |   ITEM_TYPE
--------------------------------------------------------------------
CAT                            NULL                       P
BROOM                          NULL                       H
LEGO                           NULL                       T
BATTERY                        NULL                       *A
GIFT CARD                      NULL                       *A
PARROT                         NULL                       P
STORE2 ONLY ITEM                2                         *SP

I want to output all the items for every store:
STORE_NAME    |  ITEM_NAME     |      ITEM_TYPE    
-------------------------------------------------
STORE1              BATTERY              *A
STORE1              GIFT CARD            *A
STORE1              LEGO                 *T
STORE1              CAT                  *P
STORE1              PARROT               *P

STORE2              BATTERY              *A
STORE2              GIFT CARD            *A
STORE2              STORE2 ONLY ITEM     *SP

STORE3              BATTERY              *A
STORE3              GIFT CARD            *A
STORE3              LEGO                 *T

STORE4              BATTERY              *A
STORE4              GIFT CARD            *A

STORE5              BATTERY              *A
STORE5              GIFT CARD            *A
STORE5              CAT                  *P
STORE5              PARROT               *P
STORE5              BROOM                *H

STORE6              BATTERY              *A
STORE6              GIFT CARD            *A

So what is happening? I have some stores. I have some items. Those items have a type. For example, LEGO, is an item with a type of toy. There are some items that have a "special" type. These are prefixed with a '*'. The *A type stands for "all". This means this item should be in every single store. There is also the type *SP which says this item should only be in one specific store and that store is indicated in the ITEMS table.
The query I am trying to do is to show all the items each store has.
The problem I am facing is the STORES_ITEM_TYPES table doesn't specify the special types (*A and *SP). This is because these types are assumed inherently already be part of all the stores, so they don't need to be specified (this is not my data model).
I tried the following query:
SELECT * 
FROM   stores p, 
       stores_item_types t, 
       items r, 
       item_types y 
WHERE  stores.store_id = stores_item_types.store_id 
       AND stores_item_types.item_type = items.item_type 
       AND stores_item_types.item_type = item_types.item_type

I am getting all the items EXCEPT the special ones (*A and *SP). So my output is currently like so:
STORE_NAME    |  ITEM_NAME     |      ITEM_TYPE    
-------------------------------------------------
STORE1              LEGO                 *T
STORE1              CAT                  *P
STORE1              PARROT               *P

STORE3              LEGO                 *T

STORE5              CAT                  *P
STORE5              PARROT               *P
STORE5              BROOM                *H

Here is a sqlfiddle:  sqlfiddle.com/#!9/cae02d/1

A: NOTE: The previous version of this answer tried (rather clumsily) to get around the limitations of the unusual data model. I think the following one is a better, clearer, approach.
The main issue here is that the data is not exactly normalized, especially with regards to the association between a store and an item type. My suggestion is to use two subqueries to obtain a clearer version of stores_item_types and items, and then make simple INNER JOINs.
Let's start with stores_item_types. We want our derived table to say explicitly that each store also carries the *A type, and that each store also carries its own *SP type. For example:
SELECT *
FROM stores_item_types sit
UNION
SELECT store_id, '*A'
FROM stores
UNION
SELECT store_id, CONCAT('*SP', store_id)
FROM stores

will produce something like this:
STORE_ID    ITEM_TYPE
1           *A
1           *SP1
1           P
1           T
2           *A
2           *SP2
3           *A
3           *SP3
...         ...

and so on. We also need a derived version of items, to match the new types *SP1, *SP2, etc.
SELECT
  item_name,
  specific_store_id,
  CONCAT(item_type, IFNULL(specific_store_id, '')) item_type
FROM items

The final query would then look like this:
SELECT
  s.name store_name,
  i.item_name,
  i.item_type
FROM stores s
INNER JOIN (
  SELECT *
  FROM stores_item_types sit
  UNION
  SELECT store_id, '*A'
  FROM stores
  UNION
  SELECT store_id, CONCAT('*SP', store_id)
  FROM stores ORDER BY store_id, item_type
) sit ON s.store_id = sit.store_id
INNER JOIN (
  SELECT
    item_name,
    specific_store_id,
    CONCAT(item_type, IFNULL(specific_store_id, '')) item_type
  FROM items
) i ON i.item_type = sit.item_type
ORDER BY s.store_id, sit.item_type

You can see this query in action in this fiddle.

A: Could you check if it meets your expectations,
I have used union all to differentiate 3 different item_types
First union: for those where we have matched items in stores_item_types and exculde others like *A and *SP.
Second union: for only item_type = '*A' and did a cross join with all available store ids.
Third union: for only item_type='*SP' and join with store id to include to the specific store.
select *
  from
(
-- for all matched items in store_item_types
select st.id,st.name,it.item_name,it.item_type
  from stores st
  join stores_item_types sit
    on st.id = sit.store_id 
   and sit.item_type not in ('*A','*SP')
  join items it
    on sit.item_type = it.item_type
  join item_types ity
    on sit.item_type = ity.type
union all
-- item type *A for all stores 
select st.id,st.name,it.item_name,it.item_type
  from stores st
  cross join items it
     on it.item_type = '*A'
union all
-- specific items for specific store
select st.id,st.name,it.item_name,it.item_type
  from stores st
  join items it
    on it.specific_store_id = st.id
where it.item_type = '*SP'
) a
order by id,case item_type
              when '*A' then 1
              when 'H' then 2
              when 'P' then 3
              when 'T' then 4
              when '*SP' then 5
              else 6
            end;

Note: the extra order by I put for my convenience to see the output which can be removed. And I have only taken columns which are necessary as per the expected output and others can be added if required.
Db<>fiddle for reference
