Join stored procedure - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Join stored procedure

Author

Topic

igalbk
Starting Member

9 Posts

Posted - 2010-09-20 : 10:12:07

Hi.
I'm trying to join few tables.
one of the table is empty (no rows)
Empty table name IDs1 but it can be any other table from IDs1...n.
I get good result when non of the tables are null. but,I get null table when I'm running this stored procedure with emty table.
How can I not include tables to join on the stored procedure that are empty?
Here is the select from the stored procedure.


    SELECT * FROM  _BR
    INNER JOIN IDs1 p ON _BR.Prj_ID = p.ID 
    INNER JOIN IDs2 w ON _BR.WID = w.ID

Thanks for help.

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-20 : 10:15:35

if your _BR is table is not going to be empty then use Outer Join

SELECT * FROM _BR
LEFT JOIN IDs1 p ON _BR.Prj_ID = p.ID
LEFT JOIN IDs2 w ON _BR.WID = w.ID

igalbk
Starting Member

9 Posts

Posted - 2010-09-20 : 10:24:40

quote:
Originally posted by rohitvishwakarma

if your _BR is table is not going to be empty then use Outer Join

SELECT * FROM _BR
LEFT JOIN IDs1 p ON _BR.Prj_ID = p.ID
LEFT JOIN IDs2 w ON _BR.WID = w.ID

Thanks for your answer.
the _BR table is never empty.
When I tried to do you code I get the whole data from _BR table
IDs1 -empty
IDs2 -has one ID
any ideas?
Thanks.

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-20 : 10:28:48

If you are going to use LeftJoin then all the rows from Left table i.e. _BR will be selected and matching rows from Ids1 & Ids2 will be taken. Since, Ids1 is empty the corresponding values will be NULL & for Ids2 the matching ID data will be there.

igalbk
Starting Member

9 Posts

Posted - 2010-09-20 : 10:35:04

quote:
Originally posted by rohitvishwakarma

If you are going to use LeftJoin then all the rows from Left table i.e. _BR will be selected and matching rows from Ids1 & Ids2 will be taken. Since, Ids1 is empty the corresponding values will be NULL & for Ids2 the matching ID data will be there.

OK.
That's what I thought. but this code selects all 106 rows from _BR table, although only 7 are matching to the values from table IDs2
basically what it does is select * from _BR.
please help.

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-20 : 10:37:29

SELECT * FROM _BR
LEFT JOIN IDs1 p ON _BR.Prj_ID = p.ID
INNER JOIN IDs2 w ON _BR.WID = w.ID

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-20 : 10:42:08

You want to say is that suppose in future there may be a situation when any of the tables Ids1,Ids2.. Idsn can be empty and

1) IF Ids1 is empty
Effectively your query should be:

SELECT * FROM _BR
INNER JOIN IDs2 w ON _BR.WID = w.ID -- It will give you 7 records

2) IF Ids2 is empty
Effectively your query should be

SELECT * FROM _BR
INNER JOIN IDs1 p ON _BR.Prj_ID = p.ID -- It will give you 10 records(assuming 10 matching id's are in Ids1)

3) IF none is empty

SELECT * FROM _BR
INNER JOIN IDs1 p ON _BR.Prj_ID = p.ID
INNER JOIN IDs2 w ON _BR.WID = w.ID -- (give all the matching records present in the 3 tables)

Is this what you want to achieve?

igalbk
Starting Member

9 Posts

Posted - 2010-09-20 : 10:48:12

quote:
Originally posted by rohitvishwakarma

SELECT * FROM _BR
LEFT JOIN IDs1 p ON _BR.Prj_ID = p.ID
INNER JOIN IDs2 w ON _BR.WID = w.ID

rohitvishwakarma,
thanks for your help. but now I'm getting the expected result (7 desired rows) but the problem is that I don't know which of the tables are null during the coding.
I can get IDs2 empty table and in this case INNER JOIN won't help.
is there a way to distinguish which tables will be INNER JOIN or LEFT JOIN (suppose I know how to know which tables are empty during run time)
Please advise.

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-20 : 10:48:54

If the above is your requirement then

DECLARE @sql VARCHAR(1000)

SET @sql = 'SELECT * FROM _BR '

IF( SELECT COUNT(*) FROM IDs1 > 0 )
SET @sql = @sql + ' INNER JOIN IDs1 p ON _BR.Prj_ID = p.ID '

IF( SELECT COUNT(*) FROM IDs2 > 0 )
SET @sql = @sql + ' INNER JOIN IDs2 w ON _BR.WID = w.ID

EXEC(@sql)

igalbk
Starting Member

9 Posts

Posted - 2010-09-20 : 11:05:33

quote:
Originally posted by rohitvishwakarma

If the above is your requirement then

DECLARE @sql VARCHAR(1000)

SET @sql = 'SELECT * FROM _BR '

IF( SELECT COUNT(*) FROM IDs1 > 0 )
SET @sql = @sql + ' INNER JOIN IDs1 p ON _BR.Prj_ID = p.ID '

IF( SELECT COUNT(*) FROM IDs2 > 0 )
SET @sql = @sql + ' INNER JOIN IDs2 w ON _BR.WID = w.ID

EXEC(@sql)

Unbelievable!
you really helped me.
It works. and I learned new method building queries from string.
Thanks a lot!!!

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-20 : 11:20:03

Always Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources