Join Help - SQL Server Forums

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

Join Help

Author

Topic

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-04-16 : 11:54:03

Hoping someone can help a newbie to SQL.

I have a query with 3 tables, System, System 1 and Lookup. System contains versioned data for various systems, System1 contains the same data but for the previous version of the system, and the lookup table contains display data for various codes in the system and system 1 tables.

I am trying to do a very simple report where by I can see the current and previous value of a particular system attribute as well as the display value (rather than code) of this attribute.

I can get the current and previous values OK, however when I join the System and System 1 tables to the lookup table, version 1 dissapears.

I would imagine this is beacause the previous version to version 1 is does not exist therefore I cant lookup that value to return the display value.

Below is the basic code I have been using. I have modified it somewhat to take out client specific info, so apologies if the names don't quite tally.

Hopefully someone has a suggestion as to how I can fix this

Thanks in advance

SELECT dbo.System.name AS System, System_1.name AS PrevSystem, dbo.LookupTable.Value, LookupTable_1. Value AS PrevValue
FROM dbo.LookupTable AS LookupTable_1 INNER JOIN
dbo.LookupTable INNER JOIN
dbo.SystemINNER JOIN
dbo.System AS System_1 ON dbo.System.ID = System_1.ID AND dbo.System.Version = System_1.Version - 1 ON
dbo.LookupTable.Value = dbo.System.value ON LookupTable_1.Value = System_1.value

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-16 : 12:01:14

can you post some data from tables to give us an indication on how version data exists and also codes?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-04-16 : 13:03:18

System and System1 tables have the same columns, however System1 is the previous version of system, eg what a value previously was. They are joined where System.version = System1.version -1.

In both these tables the data is versioned, so when a any data in the system is changed a new version is created and a new line in the table is added.

In the report I want to show the data from the current version and the previous version. All the data in these tables are in codes, not what is seen in the UI of the system. So the code 1 = Maximum Value in the system UI, code 2 = Minimum Value in the system UI etc.

The problem is when a value only has 1 version, the previous version is NULL, therefore there is nothing to lookup to return the value that would be displayed in the UI. I just need this to show as Null or blank in the report.

The way it is set up at the moment, the report will not display version 1.

Trying to paste in some sample data to this reply, but the formatting goes wonky and you cant really see what values are in what column - sorry

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-16 : 14:38:16

ok. in that case you should be changing join with System1 to a left join. Also the join to lookup based on system1 should also be left join

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-04-16 : 14:43:24

Thanks - mistyped the join with System to System1, it is already a Left Join.

I thought I had tried doing a left join with the lookup table, but knowing my luck I have probably done it somewhere else!!!

I will give that a go in the morning

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-16 : 14:47:43

ok...that should fix it. Also if you've any filter conditions using WHERE clause make sure you dont include any fields from left joined table on it otherwise it will get reduced to inner join

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-04-17 : 01:55:38

What if I need to use a filter at some point, how can I get it to work?

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-17 : 02:25:47

You should post the DDL of the tables and some sample data and also the expected resultset. With the expected result set tell us about the filters you have in mind.
Without getting to know what data you are looking at, we can't tell you how to get to a solution.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-04-17 : 02:46:30

I don't see how the actual data is relevant, but again I am a novice.

Essentially the system usese the same code number for different parts of the system, but the where clause allows me to restrict what is looked up to a certain portion of the system that my report deals with.

Let's say the codes are 1-10, with each code In the lookup attached to a display value and a category, so the codes can be used again.

So code 1 category 1 is for example max value, but code 1 category 2 is weekly value. For my report I want to filter only on 1 category

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-17 : 04:32:49

From what i understand(ie : without DDL and sample data), you see some records when you join System and System1 tables. But, these records vanish when you extend the join query for other tables. Right??

This may be because of the reason that the records which were selected from System and System1 are not present in the Lookup table.
And the filters you were talking about....i hope they were a part of both the queries.

Plus I would still ask you to post DDL and sample data so that we could have a look at what you are looking at. Would make things much easier.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-04-17 : 04:46:42

You are correct

In my report I should see 10 entries, System A 5 versions and System B 5 versions.

The lookup is attached to both tables to show the display value rather than the code, and as a result only shows 8 entries.

The missing two entries are version 1 of both systems.

I have tried to paste sample data into previous posts, but the table format is not accepted and the data is hard to read. I will reply with the DDL ASAP though.

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-04-17 : 05:24:15

Here is the DDL

SELECT dbo.Treatment.Version AS Version, dbo.Treatment.name AS Name, dbo.Treatment.call_result AS Code, dbo.LookupTable.DisplayValue, Treatment_1.Version, Treatment_1.call_result AS PrevCode, LookupTable_1.DisplayValue AS PrevDisplayValue
FROM dbo.LookupTable AS LookupTable_1 LEFT OUTER JOIN dbo.Treatment AS Treatment_1 ON LookupTable_1.Value = Treatment_1.call_result RIGHT OUTER JOIN dbo.Treatment LEFT OUTER JOIN dbo.LookupTable ON dbo.Treatment.call_result = dbo.LookupTable.Value ON Treatment_1.BCID = dbo.Treatment.ID AND Treatment_1.Version + 1 = dbo.Treatment.Version
WHERE (dbo.LookupTable.CodeSet = 'CallResult') AND (LookupTable_1.CodeSet = 'CallResult')

Here is some sample data

Name Version Code Display Value Prev Version Prev Code Prev Display Value
Treatment 1 1 5 Remote Release Null Null Null
Treatment 1 2 5 Remote Release 1 5 Remote Release
Treatment 1 3 5 Remote Release 2 5 Remote Release
Treatment 1 4 5 Remote Release 3 5 Remote Release
Treatment 1 5 5 Remote Release 4 5 Remote Release
No Answer 1 7 No Answer Null Null Null
No Answer 2 7 No Answer 1 7 No Answer

Treatment 1 version 1 and No answer version 1 are missing from teh query results when both Treatment and Treatment 1 are joined to the lookup tables. When only Treatment is joined to the lookup table, all results are displayed, however for previous value I would only see the prev Code not the Prev Display value whihc I need.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-17 : 13:04:43

quote:
Originally posted by sprotson

What if I need to use a filter at some point, how can I get it to work?

you need to specify filter condition in ON rather than using WHERE in that case as join is outer

see

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-17 : 13:10:07

[code]
SELECT dbo.Treatment.Version AS Version,
dbo.Treatment.name AS Name,
dbo.Treatment.call_result AS Code,
dbo.LookupTable.DisplayValue,
Treatment_1.Version,
Treatment_1.call_result AS PrevCode,
LookupTable_1.DisplayValue AS PrevDisplayValue
FROM dbo.Treatment
LEFT OUTER JOIN dbo.LookupTable
ON dbo.Treatment.call_result = dbo.LookupTable.Value
AND dbo.LookupTable.CodeSet = 'CallResult'
LEFT OUTER JOIN dbo.Treatment AS Treatment_1
ON Treatment_1.BCID = dbo.Treatment.ID
AND Treatment_1.Version + 1 = dbo.Treatment.Version
LEFT OUTER JOIN dbo.LookupTable AS LookupTable_1
ON LookupTable_1.Value = Treatment_1.call_result
AND LookupTable_1.CodeSet = 'CallResult'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-04-18 : 11:20:54

Thanks, works a treat

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 01:03:30

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources