ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Zoran Avtarovski <zo...@sparecreative.com>
Subject Re: OT: Need Help with SQL Join
Date Wed, 08 Feb 2006 14:54:53 GMT
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
>> 
>> 
>>   
> 



Mime
View raw message