>> select movies.id, title, rating, tag >> from movies left join user on movies.id = user.id >> left join tag on movies.id = tag.id; >> > Ok, this works and it is fast but isn't any way to make this a little > simpler because the real query involves more tables and more columns and it > becomes a mess. I tried with using(id) but it is the same problem than with > natural left join.
No, you cannot simplify this. Maybe you better make trigger on movies so that every time new row inserted relative rows in user and tag are created with empty rating and tag value. After that you won't have to deal with left joins and with simple "natural join" you won't have such problem... Pavel On Thu, Dec 3, 2009 at 12:30 PM, Yuzem <naujnit...@gmail.com> wrote: > > Thanks both for the replies. > > Pavel Ivanov-2 wrote: >> >> If using "natural left join" is not a requirement for you then this >> works as you expect it: >> select movies.id, title, rating, tag >> from movies left join user on movies.id = user.id >> left join tag on movies.id = tag.id; >> > Ok, this works and it is fast but isn't any way to make this a little > simpler because the real query involves more tables and more columns and it > becomes a mess. I tried with using(id) but it is the same problem than with > natural left join. > > > SimonDavies wrote: >> >> SELECT a.id,a.title,a.rating,tag.tag FROM (movies natural left join >> user) as a natural left join tag; >> > It doesn't work, it says: no such column a.id > If I use movies.id I have the same problem as before: movies with no rating > don't get any tags. > > -- > View this message in context: > http://old.nabble.com/Multiples-natural-left-joins-problem-tp26627140p26629866.html > Sent from the SQLite mailing list archive at Nabble.com. > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users