How to do this Query ?

I have table structure like this :

Table Name : Info, Course

Table : Info

Name

CourseID

StudentID

Table : Course

CourseID

CourseDesc

Sample Data :

Info Table : Harsimrat Thukral, 1, 123

Something, 2, 176

Nothing, NULL, 208

Course Table Data : 1, CS121

2, CS321

Now the problem is if I do query something like this :

SELECT * FROM

Info i , Course c

WHERE i.CourseID = c.CourseID

From the above query I don't see the result where it is (NULL), how can I see all the 3 results.

Thanks for the help.

Harsimrat

You will need to use an outer join instead of an inner join. Give a look to OUTER JOINs in books online.

|||

First of all say good bye to the ancient SQL join syntax, always stick with ANSI standard. The following query can be rewritten as follow as for ANSI syntax.

Select * from info inf

Inner Join course cou on inf.[CourseID] = cou.[CourseID]

To get all the result of info even there is no match on course table, you have to use the OUTER JOIN.

Here your query,

Code Snippet

Create Table #info (

[Name] Varchar(100) ,

[CourseID] Varchar(100) ,

[StudentID] Varchar(100)

);

Insert Into #info Values('Harsimrat Thukral','1','123');

Insert Into #info Values('Something','2','176');

Insert Into #info Values('Nothing',NULL,'208');

Create Table #course (

[CourseId] Varchar(100) ,

[Coursedesc] Varchar(100)

);

Insert Into #course Values('1','CS121');

Insert Into #course Values('2','CS321');

Code Snippet

--ANSI Standard :: Recommanded

Select * from #info inf

Left Outer Join #course cou on inf.[CourseID] = cou.[CourseID]

Code Snippet

--SQL Server Standard, please forget this syntax

--It will work fine upto SQL Server 8.0 from 9.0 onwards this syntax won’t work

Select * from #info inf, #course cou

Where

inf.[CourseID] *= cou.[CourseID]

If you want to learn all type of ANSI Join check out this thread,

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1611077&SiteID=1