Select Statement in SP 2

[stapet]

I am passing the following parameters to my stored procedure @username, @password, @source.

My select statement looks something like this

Select *
From Table1 LEFT OUTER JOIN Table2 ON Table1.name = Table2.winname
Where @username = table1.name and @password = table1.password And table2.source = @source

Example

Table1 Table2
name winname
username accessarea
password source

What I am trying to do is return all rows from table1, even if there is no source in table2, but if there is a source return only those records that match my parameter I pass.
I thought with Left Outer Join my select would return all rows from table 1 and if no matchin rows in table2 then return a null value for those columns. It works fine if I take out (where source = @source). Does anyone know if this is even possible, without returning the information in a temp table???? Thanks in advance for any help.

 

[SQLSister]

YOu could try:
Select *
From Table1 LEFT OUTER JOIN Table2 ON Table1.name = Table2.winname
Where @username = table1.name and @password = table1.password And (table2.source = @source or table2.source is null)

By the way, it is a bad practice to use Select *, much better to specify only the fields you need. Uses less network resources.

 

[stapet]

The problem is that source is never null in the table2. I am thinking that it is not possible to select my information this way. Thanks for the tip on selecting columns, I normally do select only the columns I need but wrote it this way to save on typing.

 

[SQLSister]

Did you try the code? WHen you have a left join the fields with no value in the joined table are null and can be searched as such which is why a statement such as the one below will get you the reords which are in the first table but not the second table:
Select a.airportId from Airport a left outer join dbo.AiportAdminRights b
on a.airportId = b.airportId where b.AirportId is null

I tried something simliar to the code above on two of my tables and it pulled the records for me.

 

[sunila7]

Hi,

Change ur query like this and see...


Select *
From Table1 LEFT OUTER JOIN Table2 ON Table1.name = Table2.winname And table2.source = @source
Where @username = table1.name and @password = table1.password

Is this wht u r looking for


Sunil

 

[stapet]

Thanks Sunil! It worked perfect.

 

[stapet]

Sunil

Could you please explain that join to me as far as what is happening, it works great, but I can't really figure out what is going on. Thanks again.

stapet

 

[sunila7]

Hi Stapet,

When u specify "And table2.source = @source" in where clause the data is retrived and all rows which doesnt satisfy the conditions is removed,so if there is no source for the value passed to SQL, it doesnt retrive any data....

But if u specify the condition in the left outer join clause then even if there is no condition matching the source field, Left outer makes sure that all data from the first table is retireved

Hope it Helps.... thanks for the star...

Sunil