Hi Friends,

=20

I need your help to write this query.

=20

I have two tables tMember & tFavorites.

=20

Data:

Member A has 3 favorites P, Q & R.

Member B & C have no favorites.

=20

Stored Procedure:

CREATE PROCEDURE sp_SearchResults

(

@favorites_only char(1) =3D null

)

AS

begin

SELECT=20

m.member_id,

f.favorites_id

FROM tMember m

LEFT OUTER JOIN tFavorites f ON m.member_id =3D f.member_id

Where

/* Condition 1 - retrieve all members */

(@favorites_only is null AND f.favorites_id is null)

or=20

/* Condition 2 - retrieve only favorites */

(@favorites_only =3D 'Y' AND m.member_id =3D 'A')

End

=20

Condition 2 is fine.

=20

But, in Condition 1 the query excludes members who have favorites.

But here I need all members to be displayed i.e. in this case, I want =
Member A also to be retrieved).

=20

Please advice.

=20

Rajan
Please paost DDL + sample data
I guess you may try=20
/* Condition 1 - retrieve all members */
(@favorites_only is null AND f.member_id is null)

[quoted text, click to view]
Hi Friends,

=20

I need your help to write this query.

=20

I have two tables tMember & tFavorites.

=20

Data:

Member A has 3 favorites P, Q & R.

Member B & C have no favorites.

=20

Stored Procedure:

CREATE PROCEDURE sp_SearchResults

(

@favorites_only char(1) =3D null

)

AS

begin

SELECT=20

m.member_id,

f.favorites_id

FROM tMember m

LEFT OUTER JOIN tFavorites f ON m.member_id =3D f.member_id

Where

/* Condition 1 - retrieve all members */

(@favorites_only is null AND f.favorites_id is null)

or=20

/* Condition 2 - retrieve only favorites */

(@favorites_only =3D 'Y' AND m.member_id =3D 'A')

End

=20

Condition 2 is fine.

=20

But, in Condition 1 the query excludes members who have favorites.

But here I need all members to be displayed i.e. in this case, I want =
Member A also to be retrieved).

=20

Please advice.

=20

Hi

As Uri says posting DDL and example data would make this un-ambiguous see
http://www.aspfaq.com/etiquette.asp?id=5006.

If you want all tmember records to be retrieved if @favorites_only is null
and only records with member_id 'A' if @favorites_only = 'Y' then use

/* Condition 1 - retrieve all members */
(@favorites_only is null )
or
/* Condition 2 - retrieve only favorites */
(@favorites_only = 'Y' AND m.member_id = 'A')

If you want only matching tmember records to be retrieved if
@favorites_only is null and only records with member_id 'A' if
@favorites_only = 'Y' then use:

/* Condition 1 - retrieve all members */
(@favorites_only is null and @favorites_only is not null )
or
/* Condition 2 - retrieve only favorites */
(@favorites_only = 'Y' AND m.member_id = 'A')

John


[quoted text, click to view]
Hi Friends,

I need your help to write this query.

I have two tables tMember & tFavorites.

Data:
Member A has 3 favorites P, Q & R.
Member B & C have no favorites.

Stored Procedure:
CREATE PROCEDURE sp_SearchResults
(
@favorites_only char(1) = null
)
AS
begin
SELECT
m.member_id,
f.favorites_id
FROM tMember m
LEFT OUTER JOIN tFavorites f ON m.member_id = f.member_id
Where
/* Condition 1 - retrieve all members */
(@favorites_only is null AND f.favorites_id is null)
or
/* Condition 2 - retrieve only favorites */
(@favorites_only = 'Y' AND m.member_id = 'A')
End

Condition 2 is fine.

But, in Condition 1 the query excludes members who have favorites.
But here I need all members to be displayed i.e. in this case, I want Member
A also to be retrieved).

Please advice.

Thank you.