SQL Views 1

[chester27]

I am trying to create a view that will display data from 2 colunms in one table. In one of the columns there is generic text which is ok, but also a numbered reference to a column in another table. I would like to retain the text data but replace the numeric value with the corrosponding data description column from the seperate table to which the numeric value belongs to.

 

[r937]

sounds like a JOIN to me

r937.com | rudy.ca

 

[SQLSister]

Maybe this will help you get started. It will need adjusting depending onthe structure of your file dthat contains text and numbers

create table #temp (test1 varchar(10), temp1id int identity)
create table #temp2 (temp2id int identity, testdesc varchar(10))

insert #temp2 (testdesc)
values ('testing')

insert #temp2 (testdesc)
values ('testing2')

insert #temp (test1)
values('justatest1')

insert #temp (test1)
values('test2')

select left(test1, len(test1)-1)+testdesc
from #temp t
join #temp2 t2 on right(t.test1, 1)= t2.temp2id

"NOTHING is more important in a database than integrity." ESquared

 

[chester27]

Descr Type detail Answer
Date and Time DATE DATETIME SEP 2007 12:06
Location of GEN LOCATION 2
Futher Details TXT TEXT Bar Med
Who was GEN WHO 19
Weapon Used GEN WEAPON 7
Motive for GEN MOTIVE 9
Was Alcohol Involved BOOL Yes No No

This is the output of my view. All the answers that are Type GEN need to be replaced with the decription from another tables column. I have tried JOIN but seem to be missing something as I vary in success from nothing to hundreds of links to the same answer.

 

[gmmastros]

I suggest that you show some sample data from BOTH tables, and also show the expected results. This will help us to understand what your requirements are.

-George

"the screen with the little boxes in the window." - Moron

 

[chester27]

GLI_SEQU GLI_CODE GLI_DESCRIPTION
1 BAR Bar, Club
2 HOME Home
3 STREET Street
4 WORKPLACE Workplace
5 FIREARM Firearm
6 KNIFE Knife
7 BODY Body Part
8 BLUNT Blunt Object
9 GLASS Glass
10 BOTTLE Bottle
11 OTHWEAP Other Weapon (Specify)
12 FOOTBALL Football
13 GANG Gangs
14 TERRITORY Territory
15 RELIGION Religion
16 RACIAL Racial
17 DRUGS Drugs
18 ROBBERY Robbery
19 BOUNCER Bouncer
20 PARTNER Wife, Husband or Partner
21 STRANGER Stranger
22 FAMILY Family Member
23 CLIENT Customer and or Client
24 FRIEND Friend or Known Associate
25 NOTKNOW Not Known
26 OTHMEM Other (Specify)
27 OTHERMO Other Motive
28 UNPROVOKED Unprovoked Attack
29 OTHER PLA Other Location Not Specified

This is the other table details. I would like to replace the number value in the first table with the description value from the above table.

Descr Type detail Answer
Date and Time DATE DATETIME SEP 2007 12:06
Location of GEN LOCATION Home
Futher Details TXT TEXT Bar Med
Who was GEN WHO Bouncer
Weapon Used GEN WEAPON body part
Motive for GEN MOTIVE glass
Was Alcohol Involved BOOL Yes No No

 

[gmmastros]

The solution to your problem does involve a join (like Rudy mentioned in the first reply).

The trick here is that we don't ALWAYS want to join to the other table. Unfortunately, you cannot pick and choose when to join. You either join, or you don't. In this case, you can perform a left join to solve your problem. With a left join, you will get all the records from the 'left' table, and only matching records from the right table. If there is not a corresponding match in the 'right' table, NULL will be returned for every column. We can use this to our advantage. By putting enough conditions on the join, we can cause the query to ONLY return (i.e. match) records that we want to use. If there is no match, we want to use data from the original table. If there is a match, then use records from the second table.

So, the question then becomes, how do we choose. Like I said earlier, by left joining (and putting enough conditions on the join), we will get NULLs from the second table. When there is a NULL in the second table, we want to use the data from the first.

You never mentioned what the table names were, so in this example, I am using Table1 and Table2. Obviously, you will need to change the table names to match your real names.

Select Table1.Descr, 
       Table1.Type, 
       Table1.Detail, 
       Coalesce(Table2.GLI_DESCRIPTION, Table1.Answer) As Answer
From   Table1
       Left Join Table2
         On Table1.Type = 'GEN'
         And Table1.Answer = Table2.GLI_SEQU

I want to mention a couple things here, for clarification purposes.

First, notice the join condition. The 'Answer' column from table1 joins with the GLI_SEQU column of table 2. That's obvious. However, I added Table1.Type = 'GEN'. This ensures that we will only match records in the second table when the Type column of Table1 is 'GEN'.

Second, notice the Coalesce function. This function will return the first Non-Null value passed to it. Remember earlier when I said that NULL's will be returned when there is no match? Well, in that case Table2.GLI_DESCRIPTION will be null. The Coalesce function will then return the Table1.Answer value.

Does this make sense?

-George

"the screen with the little boxes in the window." - Moron

 

[chester27]

That has worked a treat, many thanks for that. One question to add to this. This table will be getting the same 8 questions posted with varing answers. Is there a way to put a row break between each batch of 8 questions?

 

[gmmastros]

Putting 'Row Breaks' in to the final output is a job for the front end application, not the query.

-George

"the screen with the little boxes in the window." - Moron