ibatis-user-java mailing list archives

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