Newbie: Using JOIN doesn't return data

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 2005 Forums

Transact-SQL (2005)

Newbie: Using JOIN doesn't return data

Author

Topic

jjmagro
Starting Member

5 Posts

Posted - 2010-11-18 : 18:38:17

Ok, maybe I wasn't very descriptive in the Subject of this post, the problem is simple, but I can't have any work around that actually works :(
I have a master and detail table and I want to return all the info in the master even if they don't exists in the detail table


SELECT m.Concept, sum(d.someInteger) FROM detailsTable d
OUTER JOIN mainTable m
ON m.someId = d.someId

I tried LEFT OUTER, RIGHT OUTER, I change the order of the tables queried, but always returns all the matches in detail table, (9 out of 10)
I was taking a look at some blogs about doing JOINS but nothing work so far...
I would like to put the code of the stored procedure here, but is quite large but let me tell you something about it:
I'm evaluating some parameter, and as a result, I'm creating a query as a VARCHAR, at the end, I exec the VARCHAR variable resultant query.
The stored procedure is something like this:


CREATE PROCEDURE mySP
    @someParam VARCHAR(2)
AS
DECLARE @bigQuery VARCHAR(MAX)
SET @bigQuery = 'SELECT m.mainConcept, '
IF(@someParam = 'A')
   SET @bigQuery = @bigQuery + 'SUM(d.someValue1) '
ELSE IF(@someParam = 'B')
BEGIN
   SET @bigQuery = @bigQuery + 'SUM(d.someValue1) + '
   SET @bigQuery = @bigQuery + 'SUM(d.someValue2) '
END
SET @bigQuery = @bigQuery + 'FROM detailTable d '
SET @bigQuery = @bigQuery + 'LEFT OUTER JOIN masterTable m '
SET @bigQuery = @bigQuery + 'ON m.someId = d.someMasterId '
SET @bigQuery = @bigQuery + 'GROUP BY m.mainConcpt '
EXEC (@bigQuery)

The result is always the same, doesn't matter if I put "LEFT OUTER", "RIGHT OUTER", or even "INNER JOIN", "LEFT JOIN" or whatever.

By the way, the tables that I'm using in the query, are not indexed, nor have primary keys (I don't know if this could be the problem)...

So, if someone can bring me some light about this, I really appreciate it, any help is welcome :)

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-18 : 18:56:05

SELECT m.Concept, sum(d.someInteger)
FROM mainTable m
LEFT JOIN detailsTable d
ON m.someId = d.someId
GROUP BY m.Concept

Should work

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-18 : 18:58:00

do you have to do dynamic

print out the variable and show us

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources