I’m indebted to the friend who showed me this technique. I have always felt, and still do feel, it’s a bit messy. But when it comes getting round something in an emergency, I’ve often found it so useful.

When another round of Temp Tables would further complicate things, rather than simplify things. When I felt that it was very plain and simple, what I was trying to get at. And it would still remain clear, I would use this method. Only in these circumstances, then, have I felt In-Line-Selects are a good idea.

I have used these three versions of it. Placed in these different places in the code, (1.) In the Select, (2.) In the From, and (3.) In the Where.

— Creating the demo tables and data…

Create Table [dbo].[tblPerson](

[PersonID] [int] Null,

[Firstname] [nvarchar](50) Null,

[Surname] [nvarchar](50) Null

) On [Primary]

Create Table [dbo].[tblCountry](

[CountryID] [int] Null,

[CountryName] [nvarchar](50) Null

) On [Primary]

Create Table [dbo].[tblWorkingInCountry](

[WorkingInCountry] [int] Null,

[PersonID] [int] Null,

[CountryID] [int] Null

) On [Primary]

— tblPerson

Insert dbo.tblPerson (PersonID, Firstname, Surname) Values (1, ‘May’, ‘Day’)

Insert dbo.tblPerson (PersonID, Firstname, Surname) Values (2, ‘Frank’, ‘Holiday’)

Insert dbo.tblPerson (PersonID, Firstname, Surname) Values (3, ‘Chris’, ‘Newark’)

— tblCountry

Insert dbo.tblCountry (CountryID, CountryName) Values (1, ‘France’)

Insert dbo.tblCountry (CountryID, CountryName) Values (2, ‘Canada’)

Insert dbo.tblCountry (CountryID, CountryName) Values (3, ‘Germany’)

Insert dbo.tblCountry (CountryID, CountryName) Values (4, ‘Mexico’)

— tblWorkingInCountry

Insert dbo.tblWorkingInCountry (WorkingInCountry, PersonID, CountryID) Values (1, 1, 2)

Insert dbo.tblWorkingInCountry (WorkingInCountry, PersonID, CountryID) Values (2, 1, 4)

Insert dbo.tblWorkingInCountry (WorkingInCountry, PersonID, CountryID) Values (3, 2, 1)

Insert dbo.tblWorkingInCountry (WorkingInCountry, PersonID, CountryID) Values (4, 3, 2)

Insert dbo.tblWorkingInCountry (WorkingInCountry, PersonID, CountryID) Values (5, 3, 3)

Insert dbo.tblWorkingInCountry (WorkingInCountry, PersonID, CountryID) Values (6, 3, 4)

— Here are the examples…

Select * From tblPerson

Select * From tblWorkingInCountry

Select * From tblCountry

— Example in the Select…

Select

*

From

tblPerson P

Inner Join tblWorkingInCountry WIC On P.PersonID = WIC.PersonID

Inner Join tblCountry C On WIC.CountryID = C.CountryID

— Is similar too…

Select

WIC.WorkingInCountry

,WIC.PersonID

,WIC.CountryID

,(Select PersonID From tblPerson P Where WIC.PersonID = P.PersonID) As [P-PersonID]

,(Select CountryID From tblCountry C Where WIC.CountryID = C.CountryID) As [C-CountryID]

From

tblWorkingInCountry WIC

— Example in the From:

Select

*

From

tblPerson P

Inner Join tblWorkingInCountry WIC On P.PersonID = WIC.PersonID

Inner Join tblCountry C On WIC.CountryID = C.CountryID

— Is similar too…

Select

*

From

(Select * From tblPerson) P

Inner Join (Select * From tblWorkingInCountry) WIC On P.PersonID = WIC.PersonID

Inner Join (Select * From tblCountry) C On WIC.CountryID = C.CountryID

— Example in the Where:

Select Distinct

P.PersonID

,P.Firstname

,P.Surname

From

tblPerson P

Inner Join tblWorkingInCountry WIC On P.PersonID = WIC.PersonID

Inner Join tblCountry C On WIC.CountryID = C.CountryID

Where

C.CountryName In (‘Canada’, ‘Mexico’)

— Is similar to…

Select

*

From

tblPerson P

Where

P.PersonID In

(

Select

P2.PersonID

From

tblPerson P2

Inner Join tblWorkingInCountry WIC2 On P2.PersonID = WIC2.PersonID

Inner Join tblCountry C2 On WIC2.CountryID = C2.CountryID

Where C2.CountryName In (‘Canada’, ‘Mexico’)

)

Advertisements