get data from a JOIN - 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

SQL Server 2000 Forums

SQL Server Development (2000)

get data from a JOIN

Author

Topic

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-09-29 : 11:42:17

I need to check data from two different tables. I need to be able to get the part number from both tables where the case number is the same. I am not sure on how to do this. For example, if in Table1 I have '021-987654' as a case number and '1' as a part number. And in Table2 I have '021-987654' (same as Table1) as a case number and '2' as a part number. How can I query that so I can get both those case numbers? Right now I have this, but I know it is not right:


SELECT tblCapRec.PartNumber
FROM tblCapRec
LEFT JOIN tblFeePaid
ON tblCapRec.CaseNumber = tblFeePaid.CaseNumber
WHERE tblCapRec.CaseNumber = '021-987654'

Thanks for any help!

Brenda

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-29 : 11:48:37

SELECT tblCapRec.PartNumber, tblFeePaid.PartNumber
FROM tblCapRec
JOIN tblFeePaid
ON tblCapRec.CaseNumber = tblFeePaid.CaseNumber
WHERE tblCapRec.CaseNumber = '021-987654'

Duane.

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-09-29 : 13:03:00

But what if that case does not exist in the tblFeePaid table?

Brenda

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-29 : 13:07:47

I think you want


   SELECT 'Case' AS Source, PartNumber
     FROM tblCapRec
    WHERE CaseNumber = '021-987654'
    UNION 
   SELECT 'Fee' AS Source, PartNumber
     FROM tblFeePaid
    WHERE CaseNumber = '021-987654'

Brett

8-)

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-09-29 : 13:10:30

What is AS Source do?

Brenda

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-29 : 13:12:00

'Case' AS Source its an alias for a column

Go with the flow & have fun! Else fight the flow