On Sun, Oct 18, 2009 at 12:06 PM, Igor Tandetnik <[email protected]> wrote: > P Kishor wrote: >> On Sun, Oct 18, 2009 at 11:49 AM, Igor Tandetnik <[email protected]> wrote: >>> P Kishor wrote: >>>> Given a specific user_id (say, user_id = 1) how on earth do I get the >>>> following? >>>> idea_id idea created_by_name rating >>>> ---------- ----------- --------------- ---------- >>>> 1 free coffee jim 100 >>>> 2 long breaks jim 100 >>>> 3 quit early joe NULL >>> >>> Describe in plain English precisely what you are trying to achieve. It's >>> not obvious to me what question this resultset is the >>> answer for. >>> >> >> >> Given a specific user, show me all the ideas, and, if the specific >> user has voted for an idea, show me the rating given to that idea by >> that user. >> >> So, in the above result set, all three ideas are shown, and Jim's >> ratings are shown. Since Jim didn't rate idea 3, 'NULL' is returned. > > Did too: > > INSERT INTO users_ideas (user_id, idea_id, rating) VALUES (1, 3, 50); >
That was a typo on my part. > Anyway: > > select * from ideas left join users_ideas > on (ideas.idea_id = users.idea_id and users.user_id = 1); > Thanks. The above works. > Igor Tandetnik > > -- Puneet Kishor _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
