join reducing selected data in undesired way

Platform: SQL Server 2000 (8.00.2040, SP4, Enterprise edition)

I've got a complex query I'm trying to build, which will select all requests that have a status_code of 1, and who's related incident has a manager_id of the specified value.

SELECT (columns desired)
FROM recipient p
JOIN requests r ON p.recipient_id=r.recipient_id
JOIN incident i ON r.request_id=i.request_id
WHERE i.manager_id='value' AND r.status_code = 1

manager_id is a clumn in incident, and status_code is a column in requests

Run this way, the query selects 6 records, which I would expect.

Complication:

I need additional data from 2 more tables. This is intended give me the user who performed the insert action on the incident, and their first and last name.

table actions (a) has:user_id which is FK to users tablerequest_id which is FK to requests table already in queryaction_type which will need to be constrained to a value of 1 when a.request_id=r.request_id
table users (u) has columns with user name (which will replace user_id in display)

When I add these tables to the join in the following manner, my result set goes down to 1 record.

SELECT (columns desired)
FROM recipient p
JOIN requests r ON p.recipient_id=r.recipient_id
JOIN incident i ON r.request_id=i.request_id
JOIN actions a ON a.request_id=i.request_id
JOIN users u ON a.user_id=u.user_id
WHERE manager_id='value' AND status_code = 1

While I believe I need to be specific that I want the user_id from actions that performed the action_type=1, I don't believe that's what's hindering the operation (I'd have expected to get some duplicate results).

Any thoughts?The join on the actions table or the users table is causing the result set to shrink. Try doing a join on actions and request to see if you get the right number of results.|||try this

SELECT (columns desired)
FROM recipient p
JOIN requests r ON p.recipient_id=r.recipient_id
JOIN incident i ON r.request_id=i.request_id
left outer JOIN actions a ON a.request_id=i.request_id
JOIN users u ON a.user_id=u.user_id
WHERE manager_id='value' AND status_code = 1|||Left join worked, thanks.

join question.

Hello, folks
Desc. two tables table A
CriteriaID
1
2
3
Table B
UID ResultID CriteriaID
1 1 1
2 1 2
3 1 3
4 2 1
Want to build a query which gonna show me group by ResultID only records
from table B where B.CriteriaID = {1,2,3} from table APlease don't multi-post.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Jonh Smith" <support@.rtsplus.com> wrote in message
news:OIz61p0RFHA.3076@.tk2msftngp13.phx.gbl...
> Hello, folks
> Desc. two tables table A
> CriteriaID
> 1
> 2
> 3
> Table B
> UID ResultID CriteriaID
> 1 1 1
> 2 1 2
> 3 1 3
> 4 2 1
> Want to build a query which gonna show me group by ResultID only records
> from table B where B.CriteriaID = {1,2,3} from table A
>
>|||What do mean by "group By ResultID" If you group by resultID, then you will
get only one record in the ouput for each distinct value of ResultID {1,2}
which means that in the outout SQL needs t obe told what yo put in the other
columns.
We can easily just output the ResultID...
Select ResultID From TableB
Where CriteriaID In (Select CriteriaID From TableA)
Group By ResultID
-- --
or we can Add a count of original records...
Select ResultID, COunt(*)
From TableB
Where CriteriaID In (Select CriteriaID From TableA)
Group By ResultID
-- --
or we can Add a Sum, or Avg of original UID Values...
Select ResultID, Sum(UID), Avg(UID)
From TableB
Where CriteriaID In (Select CriteriaID From TableA)
Group By ResultID
-- --
... But if you want to Groyp BY, you have to tell the query processor what
else you want besides the Group By COlumn...
"Jonh Smith" wrote:

> Hello, folks
> Desc. two tables table A
> CriteriaID
> 1
> 2
> 3
> Table B
> UID ResultID CriteriaID
> 1 1 1
> 2 1 2
> 3 1 3
> 4 2 1
> Want to build a query which gonna show me group by ResultID only records
> from table B where B.CriteriaID = {1,2,3} from table A
>
>
>|||Thank you for your sugestion ! next time i will ..
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%234gars0RFHA.164@.TK2MSFTNGP12.phx.gbl...
> Please don't multi-post.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
> "Jonh Smith" <support@.rtsplus.com> wrote in message
> news:OIz61p0RFHA.3076@.tk2msftngp13.phx.gbl...
>|||Thank you for your response
It is has to be B.CriteriaID = 1 and B.CriteriaID = 2 and B.CriteriaID =
3 where {1,2,3} from table A
It is now make sense ?
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:DE24A35A-9344-4F69-8E23-F3F838B8C0B6@.microsoft.com...
> What do mean by "group By ResultID" If you group by resultID, then you
> will
> get only one record in the ouput for each distinct value of ResultID {1,2}
> which means that in the outout SQL needs t obe told what yo put in the
> other
> columns.
> We can easily just output the ResultID...
> Select ResultID From TableB
> Where CriteriaID In (Select CriteriaID From TableA)
> Group By ResultID
> -- --
> or we can Add a count of original records...
> Select ResultID, COunt(*)
> From TableB
> Where CriteriaID In (Select CriteriaID From TableA)
> Group By ResultID
> -- --
> or we can Add a Sum, or Avg of original UID Values...
> Select ResultID, Sum(UID), Avg(UID)
> From TableB
> Where CriteriaID In (Select CriteriaID From TableA)
> Group By ResultID
> -- --
> ... But if you want to Groyp BY, you have to tell the query processor what
> else you want besides the Group By COlumn...
> "Jonh Smith" wrote:
>|||Sorry, No, it doesn;t... B.CriteriaID cannot be = 1, AND = 2, AND = 3, all
at the same time...
Do you mean OR instead of of AND ?
But even then, that doesn't answer my question...
Sorry If I'm off-track, but it seems you might have some difficulties with
English.. Do you understand my question about "Group By" ? If Not, get
someone at your site who is a bit more fluent in English to read it and
explain it to you...
"Jonh Smith" wrote:

> Thank you for your response
> It is has to be B.CriteriaID = 1 and B.CriteriaID = 2 and B.CriteriaID
=
> 3 where {1,2,3} from table A
> It is now make sense ?
>
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:DE24A35A-9344-4F69-8E23-F3F838B8C0B6@.microsoft.com...
>
>|||I mean 'AND', .. so this is the point of my problem.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:9182725D-9E48-48D0-9430-F2F0C3D66491@.microsoft.com...
> Sorry, No, it doesn;t... B.CriteriaID cannot be = 1, AND = 2, AND = 3,
> all
> at the same time...
> Do you mean OR instead of of AND ?
> But even then, that doesn't answer my question...
> Sorry If I'm off-track, but it seems you might have some difficulties with
> English.. Do you understand my question about "Group By" ? If Not, get
> someone at your site who is a bit more fluent in English to read it and
> explain it to you...
> "Jonh Smith" wrote:
>|||I guess he is talking about relational division.
Relational Division
http://www.dbazine.com/ofinterest/o...br />
division
Example:
select
b.ResultID
from
tableB as b
inner join
tableA as a
on b.CriteriaID = a.CriteriaID and (a.CriteriaID in (1, 2, 3))
group by
b.ResultID
having
count(distinct b.CriteriaID) = count(distinct a.CriteriaID)
and count(distinct a.CriteriaID) = 3;
AMB
"CBretana" wrote:
> Sorry, No, it doesn;t... B.CriteriaID cannot be = 1, AND = 2, AND = 3, al
l
> at the same time...
> Do you mean OR instead of of AND ?
> But even then, that doesn't answer my question...
> Sorry If I'm off-track, but it seems you might have some difficulties with
> English.. Do you understand my question about "Group By" ? If Not, get
> someone at your site who is a bit more fluent in English to read it and
> explain it to you...
> "Jonh Smith" wrote:
>|||Case closed, thank you Alejandro Mesa.
This is solution
SELECT B.ResultID FROM A
JOIN B ON B.CriteriaID = A.CriteriaID
GROUP BY B.ResultID
HAVING (COUNT(B.ResultID) = (SELECT COUNT(CriteriaID ) FROM
A))
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:6D45BAB4-B39E-41DF-836E-8E2F50AA596D@.microsoft.com...
>I guess he is talking about relational division.
> Relational Division
> http://www.dbazine.com/ofinterest/o... />
20division
> Example:
> select
> b.ResultID
> from
> tableB as b
> inner join
> tableA as a
> on b.CriteriaID = a.CriteriaID and (a.CriteriaID in (1, 2, 3))
> group by
> b.ResultID
> having
> count(distinct b.CriteriaID) = count(distinct a.CriteriaID)
> and count(distinct a.CriteriaID) = 3;
>
> AMB
>
> "CBretana" wrote:
>|||Alej,
Got it now... Didn't understand...
On Celko's article (yr Link), I was familiar with the problem, but did
not know it was called "Relational Division".
I have in past used the "Not Exists... Where Not Exists..." syntax for
this issue, but I was unfamiliar with the approach you used. Thanks...
In his article, Celko makes the point that the two approaches return
different results when the divisor is empty, The nested Not Exists return
ALL records, and the Having Count(*)... approach returns an empty set...
On a purely academic note, I kinda think the former, nested approach,
which returns all records, is more mathematically "accurate"... Using Joes
example, All the pilots have the skill to fly "every" plane in an empty
hanger ...

Join only returns the read rows :|

Hi all,
I am trying to build a association table (t2) to store a list of usershave viewed an item in my records table (t1). My goal is to send theUserID parameter to the query and return to the user a read / not readmarker from the query so I can handle the read ones differently in my.net code. The problem is that I cannot work out how to return anythingbut the read data to the client. So far my stored proc looks like this
DECLARE @.UserID AS Int -- FOR TESTING
SET @.UserID = 219 -- FOR TESTING
SELECT t1.strTitle, t1.MemoID, Count(t2.UserID) AS ReadCount,t2.UserID
FROM t1
LEFT OUTER JOIN
t2 ON t1.MemoID = t2.MemoID
WHERE t2.UserID = @.UserID
GROUP BY t1.MemoID, t1.strTitle,t2.UserID
It works fine but only returns those records from t1 that are read. Ineed to return the records with null values also! I may have built theassoc table wrong and would really appreciate some pointers on what Iam doing wrong. (assoc table has rID, MemoID and UserID columns)
Please help!
Many thanks

Instead of this:

LEFT OUTER JOIN
t2 ON t1.MemoID = t2.MemoID
WHERE t2.UserID = @.UserID

Do this:

LEFT OUTER JOIN
t2 ON t1.MemoID = t2.MemoID AND t2.UserID = @.UserID

Placing the t2.UserID = @.UseriD in the WHERE limits your results toonly those rows where there is a match in t2. You could havealternately coded it as:

WHERE t2.UserID = @.UserID OR t2.UserID IS NULL

|||Thank you ever so much!
I was racking my brains on how to get around the WHERE limitation I had imposed.
Easy when you see how.
Thank you again!