Hi Zoran,
Try moving the status criteria for UserCats to the LEFT OUTER JOIN
instead of the WHERE:
SELECT *
FROM Users as U LEFT OUTER JOIN
UserCat as UC on U.user_id = UC.user_id AND UC.status = 1
WHERE U.status = 1
Keeping UC.status in the WHERE clause would filter records from the
temporary results table, so you'd only see results for U1 and U2,
because null isn't 1. With UC.status as a criteria to the LEFT OUTER
JOIN, we tell the DB how to construct the temporary results table,
meaning we only want to see 1 or null for UserCats.status. Then the
WHERE clause is applied to the temporary results table, which filters
out all Users with a status other than 1.
Ted
On 08/02/06, Zoran Avtarovski <[EMAIL PROTECTED]> wrote:
> I tried that but that's not what I need.
>
> If I had three users (U1, U2, U3).
> And their status = 1.
>
> They each have three children UserCats U1C1, U1C2, U1C3, U2 C1, U2C2 , ...,
> U3C3
>
> All of U1 usercats have status = 1, U2 has 1 usercat status = 1 and U3 has
> none,
>
> I want to get a table like
> User_id, status, user_id, cat_id, status
> U1, 1, U1, U1C1, 1
> U1, 1, U1, U1C2, 1
> U1, 1, U1, U1C3, 1
> U2, 1, U2, U2C1, 1
> U3, 1, -, -, -
>
> I want the users with status 1 but not any associated usercats that aren't
> of status 1.
>
> I could use a multiple sql call by using
>
> Select * from Users Where status = 1
>
> And then point the usercat property to another select
>
> Select * from UserCat WHERE user_id = #value# and status = 1
>
> But I'd prefer to use the n+1 solution
>
>
> Z.
>
>
> > And this simple statement ?
> >
> > SELECT * FROM Users as U
> > left outer join UserCat as UC on U.user_id = UC.user_id
> > WHERE U.status = 1
> > AND UC.status = 1
> >
> >
> > Zoran Avtarovski a écrit :
> >> I have a parent:child table relationship using a Join SQL statement and I
> >> am
> >> having trouble getting it right. One parent many children.
> >>
> >> The starting statement is:
> >>
> >> SELECT * FROM Users as U
> >> left outer join UserCat as UC on U.user_id = UC.user_id
> >>
> >> This simple statement returns all content from Users and matched UserCat
> >> content.
> >>
> >> What I'd like to do is get all the content from Users where status = 1 and
> >> same for UserCat where status = 1. I still need all users with status = 1
> >> even if there are no matching UserCat with status = 1
> >>
> >> This gets me half way there:
> >>
> >> SELECT * FROM Users as U
> >> left outer join UserCat as UC on U.user_id = UC.user_id
> >> WHERE U.status = 1
> >>
> >> I get all Users where status =1 with the matching UserCat data.
> >>
> >> What I need is something like:
> >> SELECT * FROM Users as U
> >> left outer join ( UserCat as UC WHERE UC.status = 1 ) on U.user_id =
> >> UC.user_id
> >> WHERE U.status = 1
> >>
> >>
> >> Obviously this doesn't work, but it illustrates what I'm trying to do.
> >>
> >> I have come to the limit of my SQL knowledge and would appreciate any or
> >> suggestions people might have.
> >>
> >> Zoran
> >>
> >>
> >>
> >
>
>
>
