Need help with joins or alternative (union?)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need help with joins or alternative (union?)

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-10-02 : 10:15:50

Bob writes "The goal:
Return all rows meeting criteria from Branches
Include rows from Contact if any exist
and the Contact.Deleted (bit) field is not 1

This code almost worked, it does not return the Branch row if all rows from Contacts have Deleted = 1

    Select distinct
        B.Name, B.CompanyID, B.ID BranchID,
        C.ID ContactID, C.FirstName, C.LastName
    from branches B
        left Join Contacts C on C.Branchid = B.ID
    where b.deleted = 0
        and isnull(c.deleted,0) = 0
        and b.ID in
            (Select distinct [ID] from Branches
                where Name like 'daves%')
    Order by B.Id, B.ContactID

Then I tried this, which works but returns one "extra" row of all null contact info if there are any rows in Contacts with Deleted = 1

    Select distinct
        B.Name,B.CompanyID,B.ID BranchID,
        (Case c.deleted
            when 0 then C.ID
            else NULL end) as ContactID,
        (Case c.deleted
            when 0 then C.FirstName
            else NULL end) as FirstName,
        (Case c.deleted
            when 0 then C.LastName
            else NULL end) as LastName
    from branches B
        left Join Contacts C on C.Branchid = B.ID
    where b.deleted = 0
        and b.ID in
            (Select distinct [ID] from Branches
                where Name like 'daves%')
    Order by B.Id, B.ContactID

I know there must be another approach to doing this that will give me the data as I need it. I'm too new at SQL to know what it is. If anyone can help, or has suggestions, please tell me. I'm about due to learn something new about SQL anyway. :)

BTW, this is going into a stored procedure. I've considered using a temp table and filtering out the extraneous rows but I thought someone here would have a better idea.

Thanks in advance,

Bob R"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources