Return boolean in SQL if the foreign key is found 1

[MarkZK]

Hi all,

I'll try and explain this the best way I can, but, feel free to ask if I'm not clear enough.

I have two tables and I want to return all of the data in table 1 and at the same time check if there's more data that is in table 2 linked to the rows via foreign key, so basically I'd have all the data in table 1 and a true or false depending on whether or not a foreign key on table 2 matches a primary key on table 1.

I'm not sure if this can be done with a single statement, but here is an example of the tables.

Table1

RowID(pk)	FirstName	LastName	DateOfBirth		Email		
--------------------------------------------------------------------------------------------
1		David		Stonewall	01/03/1954	david@sql-tutorial.com
2		John		Smith		12/12/1969	john.smith@john-here.com
3		Susan		Grant		03/03/1970	susan.grant@sql-tutorial.com
4		Stephen		Grant		03/03/1974	sgrant@sgrantemail.com
5		Paul		O'Neil		09/17/1982	paul.oneil@pauls-email.com

Table2

RowID	InfoID(fk)	BookedDate	AmountOfWeeks
-----------------------------------------------------
1	2		11/01/2009	2
2	5		02/03/2009	1

so, in theory I'm trying to get it to return...

RowID(pk)	FirstName	LastName	DateOfBirth		Email			MoreData
----------------------------------------------------------------------------------------------------------
1		David		Stonewall	01/03/1954	david@sql-tutorial.com		false
2		John		Smith		12/12/1969	john.smith@john-here.com	true
3		Susan		Grant		03/03/1970	susan.grant@sql-tutorial.com	false
4		Stephen		Grant		03/03/1974	sgrant@sgrantemail.com		false
5		Paul		O'Neil		09/17/1982	paul.oneil@pauls-email.com	true

Thanks for your time.

 

[bborissov]

SELECT Table1.*,
       CASE WHEN Table2.InfoId IS NOT NULL
            THEN CAST(1 AS bit)
       ELSE CAST(0 AS bit) END AS MoreData
FROM Table1
LEFT JOIN (SELECT DISTINCT InfoId
                  FROM Table2) Table2
     ON Table1.RowId = Table2.InfoId

I use derived table with DISTINCT clause in it because you can have more than one record in Table2 with the same InfoId.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[MarkZK]

I won't profess to understanding that, I just know it works Thank you very much, Borislav.

 

[bborissov]

OK, let me try to be like George (don't expect his expertise though)

1. I select ALL records from your main table (Table1)

2. I select only distinct records from Table2 based on its foreign key. If I didn't so that you may have duplicate records in your final resultset because of the join.
That is why I use so called derived table (the inline SLECT in JOIN or FROM area).

3. I use LEFT JOIN because than means: Give me ALL records from the main table (that in FROM clause) and only matching records in joined table. If there is no record matched the returned value is NULL.

I'm not sure if I made this clearer or made it even complicated . I wish I have the George's explaining skills.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[MarkZK]

no, you did a great job :-D Thanks again, it was very good of you to take the time out to extend on that, I appreciate it.

 

[Thargy]

Mark,

I am intrigued by your question, as usually we write code to satisfy a business requirement, and never in my life have I heard a business requirement that mentioned foreign keys.

Would you mind sharing the functional requirement with us, as sometimes a fresh viewpoint can help with thinking of innovative solutions, and more importantly, simplify code.

My curiosity bump needs a good scratch, so could you oblige?

Regards

T

 

[gmmastros]

Boris,

I think your explanation is awesome. Keep it up.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MarkZK]

Hello, Thargtheslayer

I fear I'm not going to be able to offer you any new fresh view points, as I'm really just a newbie trying to learn MS SQL and I'm not doing this for a business application, more as a learning exercise.

The whole foreign key scenario for these tables came about from watching a video tutorial that (if you're interested) can be found here ...

http://video.msn.com/video.aspx?mkt=en-us&vid=e7391f24-37fc-4e40-9147-be75e01adcb7

I'm trying to follow a few different directions to gain a broader understanding, I'm sure I'll do many things the wrong way first such is life.