81546

SQLite no such column from right side of nested inner join

Question:

I have three tables in a SQLite DB, Task, Project, and User, all associated with one another in that order. I would like to select ALL Tasks, and if the Project the Task is associated with has a User I would like to see both the User and the Project, otherwise I do not want the Project at all.

My first pass looked like

SELECT `Task`.`id` ,`Task`.`title` ,`Task`.`projectId` ,`Project`.`id` AS `Project.id` ,`Project`.`title` AS `Project.title` ,`Project`.`userId` AS `Project.userId` ,`Project.User`.`id` AS `Project.user.id` ,`Project.User`.`username` AS `Project.User.username` FROM `Task` AS `Task` LEFT OUTER JOIN `Project` AS `Project` ON `Task`.`projectId` = `project`.`id` INNER JOIN `User` AS `Project.User` ON `Project`.`userId` = `Project.User`.`id` AND `Project.User`.`username` = 'test01';

but the inner join "rippled" back up and I only got Tasks that had a Project with a User. Since I wanted to get all Tasks, I added some parenthesis and got

SELECT `Task`.`id` ,`Task`.`title` ,`Task`.`projectId` ,`Project`.`id` AS `Project.id` ,`Project`.`title` AS `Project.title` ,`Project`.`userId` AS `Project.userId` ,`Project.User`.`id` AS `Project.user.id` ,`Project.User`.`username` AS `Project.User.username` FROM `Task` AS `Task` LEFT OUTER JOIN (`Project` AS `Project` INNER JOIN `User` AS `Project.User` ON `Project`.`userId` = `Project.User`.`id` AND `Project.User`.`username` = 'test01' ) ON `Task`.`projectId` = `project`.`id`;

This works great if I omit the Project.User columns from the SELECT, but I want them as well. When I run this as is I get the following error

no such column: Project.User.id

It seems that adding the parentheses has in some way hidden the User table. Is there a way to get all the behavior I want from this statement?

Answer1:

ishmaelMakitla is right. By simply changing the name of the alias it all works

SELECT `Task`.`id` ,`Task`.`title` ,`Task`.`projectId` ,`Project`.`id` AS `Project.id` ,`Project`.`title` AS `Project.title` ,`Project`.`userId` AS `Project_UserId` ,`Project_User`.`id` AS `Project_User.id` ,`Project_User`.`username` AS `Project_User.username` FROM `Task` AS `Task` LEFT OUTER JOIN (`Project` AS `Project` INNER JOIN `User` AS `Project_User` ON `Project`.`userId` = `Project_User`.`id` AND `Project_User`.`username` = 'test01' ) ON `Task`.`projectId` = `project`.`id`;

Recommend

  • How to exit out of the shell script with non zero status if the files are missing in both the machin
  • How to format dir command in cmd for XP and Win 2000
  • Haskell: Non-exhaustive pattern - Checking if list is ascending
  • Join 3 tables with Count
  • LINQ Union vs SQL Union
  • Add an index (or counter) to a dataframe by group in R [duplicate]
  • C# VS 2005: How to get a class's public member list during the runtime?
  • Rails: Include all of model A and only a subset of related model B
  • Hibernate discriminator column with table per subclass
  • SUM() data in a column based on another column data
  • PostgreSQL: update with left outer self join ignored
  • MySQL Invalid query: Too high level of nesting for select
  • Get Distinct rows from a result of JOIN in SQL Server
  • How do I conditionally select a field from one of two tables?
  • JsonMappingException: Can not deserialize instance of java.lang.Integer out of START_OBJECT token
  • How to concat Pandas dataframe columns
  • how to get username into sql trigger when multiple users signed on from asp membership
  • Basic many-to-many left join query
  • Listbox within Listbox and scrolling trouble in Windows Phone 7 Silverlight
  • Disable Enter in editText android
  • Jetty Server not starting: Unable to establish loopback connection
  • Django: Count of Group Elements
  • MongoDB in PHP using aggregate to group by _id is null not working
  • Display issues when we change from one jquery mobile page to another in firefox
  • How to redirect a user to a different server and include HTTP basic authentication credentials?
  • How to check if every primary key value is being referenced as foreign key in another table
  • How to handle AllServersUnavailable Exception
  • To display the title for the current loaction in map in iphone
  • How to get next/previous record number?
  • VB.net deserialize, JSON Conversion from type 'Dictionary(Of String,Object)' to type '
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Codeigniter doesn't let me update entry, because some fields must be unique
  • trying to dynamically update Highchart column chart but series undefined
  • python regex in pyparsing
  • Getting error when using KSoap library to consume .NET web services
  • Authorize attributes not working in MVC 4
  • EntityFramework adding new object to nested object collection
  • Busy indicator not showing up in wpf window [duplicate]
  • Python/Django TangoWithDjango Models and Databases
  • Net Present Value in Excel for Grouped Recurring CF