Outer Join - can't seem to get it to work

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Outer Join - can't seem to get it to work

Author

Topic

kumaichi
Starting Member

2 Posts

Posted - 2014-03-25 : 09:53:31

I have a table that I'm calling Metadata and another table that contains values for the Metadata, here is my schema:


USE [MyDB]
GO
/****** Object:  Table [dbo].[MetaData]    Script Date: 3/25/2014 9:35:37 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MetaData](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](255) NOT NULL,
	[Type] [nvarchar](255) NOT NULL,
 CONSTRAINT [PK_MetaData] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[MetaDataValue]    Script Date: 3/25/2014 9:35:37 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MetaDataValue](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[MetaDataId] [int] NOT NULL,
	[ComponentId] [int] NOT NULL,
	[Value] [nvarchar](255) NOT NULL,
 CONSTRAINT [PK_MetaDataValue] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[MetaDataValue]  WITH CHECK ADD  CONSTRAINT [FK_MetaDataValue_MetaData] FOREIGN KEY([MetaDataId])
REFERENCES [dbo].[MetaData] ([Id])
GO
ALTER TABLE [dbo].[MetaDataValue] CHECK CONSTRAINT [FK_MetaDataValue_MetaData]
GO

Insert some data:
Insert Into MetaData (Name, Type) Values ('Height','Pedal')
Insert Into MetaData (Name, Type) Values ('Width','Pedal')
Insert Into MetaData (Name, Type) Values ('Thickness','Pedal')

Insert Into MetaDataValue (MetaDataId, ComponentId ,Value) Values (1, 1, 1.254)
Insert Into MetaDataValue (MetaDataId, ComponentId ,Value) Values (2, 1, 2.395)
Insert Into MetaDataValue (MetaDataId, ComponentId ,Value) Values (3, 1, 0.239)
Insert Into MetaDataValue (MetaDataId, ComponentId ,Value) Values (2, 2, 4.2367)

What I'm trying to do, is always return the data in the Metadata table that matches a "Type" and I want to return the values for those items even if those items don't currently exist in the MetaDataValue table by ComponentId.

Here is the query that I've tried with no success:
select md.Name, md.Type, mdv.Value from MetaData md
Full Outer Join MetaDataValue mdv On md.Id = mdv.MetaDataId
Where md.Type = 'Pedal' And mdv.ComponentId = 2

It returns:
Name Type Value
----------------------
Width Pedal 4.2367

The results I'd like are:
Name Type Value
---------------------------
Width Pedal 4.2367
Height Pedal
Thickness Pedal

Is my schema in correct or is it my query?

Thanks in advance for any assistance.

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-03-25 : 12:16:01

Your create syntax is incorrect to start with your closing brackets should be ) not (

also, to many commas
It should be

CREATE TABLE [dbo].[MetaData]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[Type] [nvarchar](255) NOT NULL
CONSTRAINT [PK_MetaData] PRIMARY KEY CLUSTERED
)

Your Primary Key constraint will also cause an error as you are using the word Pedal over and over and this is not allowed with a constraint on the whole table, you can change it to:

CREATE TABLE [dbo].[MetaData]
(
[Id] [int] IDENTITY(1,1) NOT NULL Primary Key,
[Name] [nvarchar](255) NOT NULL,
[Type] [nvarchar](255) NOT NULL
)

CREATE TABLE [dbo].[MetaDataValue](
[Id] [int] IDENTITY(1,1) NOT NULL Primary Key,
[MetaDataId] [int] NOT NULL,
[ComponentId] [int] NOT NULL,
[Value] [nvarchar](255) NOT NULL
)
We are the creators of our own reality!

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-03-25 : 12:26:56

Your query now returns values

Name Type Value
Width Pedal 4.2367

You will only get the one row as you are specifying mdv.ComponentId = 2, and since there is only one auto number with a value of 2 in the Metadata table that's all you will get.

select md.Name, md.Type, mdv.Value from MetaData md
Full Outer Join MetaDataValue mdv On md.Id = mdv.MetaDataId
Where md.Type = 'Pedal' And mdv.ComponentId = 2
You can change the AND to OR to get more rows here:

select md.Name, md.Type, mdv.Value from MetaData md
Full Outer Join MetaDataValue mdv On md.Id = mdv.MetaDataId
Where md.Type = 'Pedal' OR mdv.ComponentId = 2

This will return 4 rows as there are 2 metadataid with a number 2:

Name Type Value
Height Pedal 1.254
Width Pedal 2.395
Width Pedal 4.2367
Thickness Pedal 0.239

We are the creators of our own reality!

kumaichi
Starting Member

2 Posts

Posted - 2014-03-25 : 13:10:48

Thanks sz1 for the reply. I guess this isn't going to work for me then, gonna have to come up with a different way to do it.

My goal was to have multiple components use the same Metadata fields and be able to set different values for those fields. So I can have multiple 'Pedal' objects that are used throughout my application but the values can all be different and I still wanted to show all my Metadata fields for a given 'Pedal' even if some of the values weren't set.

Thanks again.

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-03-26 : 06:37:32

No probs, what you want is to have a unique number for your items so Pedal has for example its own stock ID field, HandBreak another items would have its own stock inventory ID, you can then query the tables by selecting the stock ID, so using your meatadataid as stock you should be able to achieve it but the table(s) design needs more working out. Basically you need your Metadata table ID needs to have a unique way of referring to the other table, you use autonumber for Pedal table but Pedal could be a unique item in the table therefore having its own unique stockID...

We are the creators of our own reality!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources