FULL OUTER JOIN not working - why? 1

[JohnDTampaBay]

I am working on metadata that uses two tables:

Properties table

  PropertyName      IsDefault
  ------------------------------
  Source            Y
  Description       Y

MetaData table

  ObjectName        PropertyName    PropertyValue
  ----------------------------------------------------------
  Inventory         Description     What we have on hand
  Inventory         Keywords        Goods Stuff Etc.

The application (of which I have no control) allows the user to choose a property from a drop-down box (from Properties) or type one in manually. So the Metadata table contains values in PropertyName that aren't in the Properties table. The Properties table contains a list of some, but not all, properties.

I'd like to see the metadata including the default properties that have not been entered and manually entered properties.

Select output

  ObjectName        PropertyName    PropertyValue
  ----------------------------------------------------------
  Inventory         Description     What we have on hand
  Inventory         Keywords        Goods Stuff Etc.
  Inventory         Source          NULL

Sounds like a job for a FULL OUTER JOIN, but every attempt I've made acts more like a LEFT or RIGHT OUTER JOIN, at best.

SELECT m.ObjectName,
  IsNull(p.PropertyName, m.PropertyName) AS PropertyName,
  m.PropertyValue
FROM Properties p
FULL OUTER JOIN Metadata m 
  ON p.PropertyName = m.PropertyName

What I thought was a simple task has turned into a big hairy mess. I've twisted the code this way and that, but I am at a loss now. Can anyone lend me a hand? What am I missing? --Angel
-----------------------------------
SELECT * FROM users WHERE clue > 0
0 row(s) affected

 

[rasx]

I am trying to avoid a discusion about outer joins. But if my next question sucks then I guess I have few choices: why was a UNION query not used?
Bryan Wilhite
info@songhaysystem.com

http://www.songhaysystem.com

 

[SQLSister]

Just what result did you get?

 

[SQLSister]

Sorry, lost track a minute. I would guess you got the following result:

ObjectName PropertyName PropertyValue
----------------------------------------------------------
Inventory Description What we have on hand
Inventory Keywords Goods Stuff Etc.
Null Source NULL

This is because Source has no equivalent record in the other table, so no value for Object name could come in. So my real question is how would it know to put Inventory in object name?

 

[JohnDTampaBay]

Cha-ching! That's it, Sister. I must be suffering from cranial-sphincter syndrome. I don't know how I could have missed that. Thanks a bunch.

The UNION is the solution. Thanks for the reminder, rasx.
--Angel
-----------------------------------
SELECT * FROM users WHERE clue > 0
0 row(s) affected