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 >> >> >> >
