|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
February 3rd, 2004, 05:16 PM
|
|||
|
|||
Need help with join
Hi,
I have 2 tables that I'd like to join based on a supplied project number. Table1: Columns = ProjectNum, Name example: 01011111, Test Project 01012222, Another Project Table2: Columns = ProjectNum, DescriptionCategory, Description example: 01011111, SD, The Short Description 01011111, LD, The Long Description 01012222, LD, Also a Long Description etc... I want to supply a project number and create a record for that project number from table1 that contains a Short Description column and a Long Description Column per record. ie. I supply 01011111 and get 01011111, Test Project, The Short Description, The Long Description. So far I am able to join the tables and get a record as long as records exist in Table2. If records don't exist in Table 2 then I get an empty result set. What I want is: I supply 01012222 and get: 01012222,Another Project,'',Also a Long Descrption Thanks, for the help.
|
|
#2
February 3rd, 2004, 05:17 PM
|
|||
|
|||
|
I forgot to add that I'm using SQL Server 2000 if that matters. I can also post the sql statements that I'm using if anyone wants to see it.
Thanks,
|
|
#3
February 3rd, 2004, 05:40 PM
|
||||
|
||||
|
try using a case statement like
Code:
SELECT A.*,
Case B.ShortDescription
When '' Then ''
End,
Case B.LongDescription
When '' Then ''
End
FROM Table1 A
INNER JOIN Table2 B On (A.ProjectNum = B.ProjectNum)
ORDER BY A.ProjectNum
|
|
#4
February 3rd, 2004, 06:41 PM
|
|||
|
|||
|
thanks memnoch but that still isn't exactly what I need.
Quote:
Table2 has a project number field, a Description field and a DescriptionType field. The DescriptionType can be 'SH' or 'DT' so if a project number has a short and long(or detailed) description then table 2 has 2 records for the project num. ie. I'd have 11111, 'SH', 'this is the short description for project 11111' 11111, 'DT', 'this is the detailed description for project 11111' and I want 1 record 11111, 'this is the short description for project 11111', 'this is the detailed description for project 11111' that I can join with another table Table 1 has the project number field, project name, etc... in the end I want the record to look like 11111, 'test project name', 'this is the short description for project 11111', 'this is the detailed description for project 11111' I've used some code that creates temporary tables and joins them together again and it works as long as Table2 to contains records with the project number that you are working with. Not all projects have desriptions and the ones that don't are returning empty result sets. I'll try and play with the case statements some more but I'm pretty new to sql and don't know all you can do yet. Thanks,
|
|
#5
February 4th, 2004, 12:43 PM
|
|||
|
|||
I finally figured out what I needed to code.
Code:
declare @tmpprojnum varchar(8)
set varchar = '11111111'
select a.Projnum, a.Name, a.LongName,
SDesc = (select cast(b.Description as Varchar(8000)) from table2 b Where b.Projnum = @tmpprojnum and b.desccategory='SH'),
LDesc = (select cast(b.Description as Varchar(8000)) from table2 b Where b.Projnum = @tmpprojnum and b.desccategory='DT')
from table1 a
left join table2 b on a.Projnum = b.Projnum
Where a.projnum = @tmpprojnum
Thanks for the posts.
|
|
| Viewing: ASP Free Forums > Database > SQL Development > Need help with join |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
