I have worked myself into a tricky situation and find myself unable to fight my way out of it with limited database experience. I've had three other developers at my office take a look at this and none of us have been able to conjure a method that does what I want to do without resorting to multiple queries. I'd really appreciate any help you may have to offer.

Here is a simplified look at my data structure:

```
[REPORTS]
store_number
item
actual_inventory

[STORES]
store_number
store_attribute1
store_attribute2
store_attribute3

[PLANS]
store_attribute1
store_attribute2
store_attribute3
item
target_inventory

```

Given a store\_number, I need to be able to return all items, actual\_inventory, and target\_inventory.

Difficulty: There are items in [REPORTS] that are not in [PLANS] and vice versa.

This will return all [REPORTS] items for a store:

```
SELECT * 
FROM `REPORTS`
WHERE 
`store_number` = <<SOME NUMBER>>

```

This will return all [PLANS] items for a store:

```
SELECT * 
FROM `PLANS`
WHERE 
`store_attribute1` =  (SELECT `store_attribute1` FROM `STORES` WHERE `number` = <<SOME NUMBER>>)
and `store_attribute2` = (SELECT `store_attribute2` FROM `STORES` WHERE `number` = <<SOME NUMBER>>)
and `store_attribute3` = (SELECT `store_attribute3` FROM `STORES` WHERE `number` = <<SOME NUMBER>>)

```

I just cannot figure out how to combine them! If I perform a UNION, I cannot seem to return the \_inventory figures separately (in their own columns).

I have tried to simulate a FULL JOIN by performing two LEFT JOINS and performing a UNION. That returns results that I can't wrap my head around.

I have even tried to use VIEWS for each of those first two queries and SELECT FROM the VIEW to pull out data by STORE.

I am clearly missing something.

If I can return a list of unique items with associated target\_inventory and actual\_inventory numbers it will save me a ton of work in my application code.

Do I need to re-structure my data?