0

I'm trying to create a View in SQL Server 2008 R2, where data is extracted from two tables with dual one-to-one relationships, and I want to create two columns in the view based values from a single column in one of the tables.

The tables are currently similar to these examples:

TableA:

PrimaryKey | Name   | Value_FK | Number_FK
-------------------------------------------
66         | NameA  | 1         | 2
77         | NameB  | 3         | 4

TableB:

PrimaryKey | Value   
-------------------
1          | 238
2          | 456
3          | 100
4          | 200

The View should look like this:

 Name   | Value | Number
-------------------------
 NameA  | 238   | 456
 NameB  | 100   | 200

('Value' and 'Number' are essentially the same type, and are both found in the 'Value' column in TableB. I thought it would be more easy to distingiush between 'Value' and 'Number', than 'ValueA' and 'ValueB').

The factor that should decide which values go into Column 'Value' or Column 'Number' is the PrimaryKey in TableB and its references in either foreignkey in TableA (but both FKs shall NEVER refer to the same key).

This is likely not the most brilliant database model, having dual relationships between to tables. This is however due to mapping some C#.NET classes to a database by using ADO.NET Entity Framework, where Class A has two objects of Class B (in this case named 'Value' and 'Number'), and the database model currently constructs two relationships becuase of this. Changing this is not an option.

I've tried googling this, but I find it difficult to find an answer that I need. Especially when most results are about the opposite: Selecting multiple columns into one column.

So, how should I write the Select statement?

CREATE VIEW ViewName
AS
SELECT DISTINCT a.Name as 'Name', ????? as 'Value', ????? as 'Number'
FROM TableA a, TableB b

I am quite rusty with advanced SQL-commands. It's been over 1,5 years since last I was into something this advanced. I tried something similar to this first:

CREATE VIEW ViewName
AS
WITH Name AS
( SELECT DISTINCT a.Name FROM TableA a  )

Value AS
(
    SELECT DISTINCT b.Value as 'Value' FROM TableA a, TableB b
WHERE b.PrimaryKey = an.ValueA_FK
),
Number AS
(
    SELECT DISTINCT b.Value  as 'Number'
    FROM TableA a, TableB b
    WHERE a.PrimaryKey = an.ValueB_PrimaryKey
)
SELECT DISTINCT 
    * FROM Name, Value, Number

The result of my utterly failed attempt is like this:

 Name   | Value | Number
-------------------------
 NameA  | 100   | 200
 NameB  | 100   | 200
 NameA  | 100   | 456
 NameB  | 100   | 456
 NameA  | 238   | 200
 NameB  | 238   | 200
 NameA  | 238   | 456
 NameB  | 238   | 456

Now, any suggestiong as what to fill in the query?

Improve this question
1
  • First never use implicit joins, they are a SQL antipattern. They are more likely to be worng and much harder over time to maintain. They are also more than 20 years out of date. Commented Jun 26, 2013 at 13:59

2 Answers 2

Reset to default
4

You can reference the same table more than once in the FROM clause:

SELECT a.Name as 'Name', b1.Value as 'Value', b2.Value as 'Number'
FROM TableA a
  inner join TableB b1
          on
                a.Value_FK = b1.PrimaryKey
  inner join TableB b2
          on
                a.Number_FK = b2.PrimaryKey

I've also removed the DISTINCT, since it shouldn't be your habit to add one, and there's nothing in the question that suggests one is necessary. I've also used ANSI-Style joins. These are almost always to be preferred over the older style (where tables in the FROM clause are just separated by commas)

If it's possible that some rows in TableA have a NULL Value_FK or Number_FK, and you still want those rows in your view, you would switch one or both of the inner joins to be left joins. You would then also decide whether the output column should be NULL (in which case you're done) or some other value (in which case, you would have e.g. COALESCE(b1.Value,<Value when null>) as 'Value').

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

In this case you must refernce the table more than once. You may need left joins (and usually do inthis type of structure as there is no guarantee of a related record for any one type of data). This is one reason why the EAV model of table deisgn is a poiir one. If you have 6 differnt types of values stored there, you need six joins. If you don't know how many you will have, you will be stuck with dynamically creating the query (which of course you can't do for a view).
@HLGEM - you don't have to - you could join to it once with an OR based predicate, then do a pseudo-pivot using MAX(CASE ... to then split out the different join conditions into their separate columns. It's not an approach I'd recommend though. And you may be correct about possibly wanting LEFT JOINs, but if that a requirement, the OP hasn't indicated it.
I just brought up the left join because of the likelihood it might need to be considered, not because I thought your code was wrong.
0

This query will yield results you want:

select t1.Name, value.Value, number.Value as Number
from TableA t1
inner join TableB value on value.PrimaryKey = t1.Value_FK
inner join TableB number on number.PrimaryKey = t1.Number_FK

you over-complicated your query a lot.

Improve this answer

1 Comment

Possibly over-complicated, but this was a pseudo-code showing only the relevant data for the problem. The real query is much bigger.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.