You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Zoran Avtarovski <zo...@sparecreative.com> on 2006/02/08 15:13:39 UTC
OT: Need Help with SQL Join
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
Re: OT: Need Help with SQL Join
Posted by Ted Schrader <ts...@gmail.com>.
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 <zo...@sparecreative.com> 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
> >>
> >>
> >>
> >
>
>
>
Re: OT: Need Help with SQL Join
Posted by Zoran Avtarovski <zo...@sparecreative.com>.
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
>>
>>
>>
>
Re: OT: Need Help with SQL Join
Posted by Vincent NICOLAS <vi...@teamlog.com>.
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
>
>
>
--
Vincent NICOLAS
Teamlog, Lyon