How to join another table based on main table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2008 Forums

Transact-SQL (2008)

How to join another table based on main table?

Author

Topic

marksquall
Starting Member

17 Posts

Posted - 2012-07-26 : 22:54:03

Dear friends,

Hello and a pleasant day. I have the following table with sample dummy data (Any relation to persons living or dead is completely coincidental. )


TBLPERSON:
PERSON_ID     LAST_NAME      FIRST_NAME
---------------------------------------
1             ROGERS         STEVE
2             STARK          ANTHONY
3             PARKER         PETER


TBLAVENGERS:
PERSON_ID     AVENGERMEMBER_ID   HERO_ALIAS         STATUS
----------------------------------------------------------
1             AV000001           CAPTAIN AMERICA    ACTIVE
2             AV000002           IRON MAN           ACTIVE


TBLAPPLICANTS:
PERSON_ID     APPLICANT_ID      DATE_OF_APPLICATION
-------------------------------------------------------
1             APP-01                  2009-01-01
2             APP-02                  2010-12-01
3             APP-03                  2012-01-01

Assuming in my scenario, all heroes who will apply for membership will have a record in TBLPERSON and TBLAPPLICANT, and once they are accepted, they will have their record on TBLAVENGERS, BUT their record in TBLAPPLICANT will not be deleted for reference purposes.

Now how would I select the first name,last name, hero alias of a person, but if he/she does exist in TBLAVENGERS, I would say "AVENGER MEMBER", or else "APPLICANT".

Mi initial idea is to search for name in TBLPERSON already INNER JOIN with TBLAVENGERS and INNER JOIN TBLAPPLICANT and if AVENGERMEMBER_ID is NOT empty, I will display "AVENGER MEMBER." (this will be actually application driven, my C# program that is).

I tried this SQL statement first before anything else:


SELECT p.FIRST_NAME, p.LAST_NAME, a.AVENGERMEMBER_ID,  b.APPLICANT_ID
FROM TBLPERSON AS p
INNER JOIN TBLAVENGER AS a
ON p.PERSON_ID = a.PERSON_ID
INNER JOIN TBLAPPLICANT AS b
ON p.PERSON_ID = b.PERSON_ID
WHERE p.FIRST_NAME='PETER' AND p.LAST_NAME='PARKER'

But the query returned no record, maybe because a.AVENGERMEMBER_ID is empty, or in short, Mr. Peter Parker has no record yet in TBLAVENGER because he is still an applicant.

I hope someone could give me information on how would I achieve my desired result.

Respectfully Yours,

Mark Squall

"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-07-26 : 23:09:36

left outer join to the optional bit (TBLAVENGER) and check the PK for null:
SELECT p.FIRST_NAME, p.LAST_NAME, a.AVENGERMEMBER_ID, b.APPLICANT_ID
FROM TBLPERSON AS p
left outer JOIN TBLAVENGER AS a
ON p.PERSON_ID = a.PERSON_ID
INNER JOIN TBLAPPLICANT AS b
ON p.PERSON_ID = b.PERSON_ID
WHERE p.FIRST_NAME='PETER' AND p.LAST_NAME='PARKER'

a.personID will be null if they are not yet an Avenger

marksquall
Starting Member

17 Posts

Posted - 2012-07-27 : 00:21:33

Dear LoztInSpace,

Wow, that was awesome!

Now the record of Mr. Peter Parker is now showing, with his AVENGERMEMBER_ID returned NULL (as I am expecting result).

But unfortunately

, if the change the query to:


SELECT p.FIRST_NAME, p.LAST_NAME, a.AVENGERMEMBER_ID, b.APPLICANT_ID
FROM TBLPERSON AS p
left outer JOIN TBLAVENGER AS a
ON p.PERSON_ID = a.PERSON_ID
INNER JOIN TBLAPPLICANT AS b
ON p.PERSON_ID = b.PERSON_ID
WHERE p.FIRST_NAME='ANTHONY' AND p.LAST_NAME='STARK'

Now the query did not return Mr. Stark's record. Is there any needed revision/s in this query so that it may still return a record whether the person is already a member of Avengers or still an applicant?

Thank you so much!

Respectfully Yours,

Mark Squall

"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20

marksquall
Starting Member

17 Posts

Posted - 2012-07-27 : 00:28:56

Dear LoztInSpace,

I got it!

...
I just used both LEFT OUTER JOIN to the two "sub" tables.

If someone has an alternative, I would love to know the "other" alternative, because I am just beginning to explore database stuffs...

Thank you so much!

Warm regards,

Mark Squall

"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources