SQL complex join query with sum
I solved it finally after fumbling about SELECT TB4.Syscode, CASE TB4.accountType WHEN 'c' THEN concat('-', (TB2.Amount - TB3.ManualDeduction)) ELSE (TB2.Amount - TB3.ManualDeduction) end AS amount FROM ( SELECT ou.OrganizationUnitID AS OUId,ou.OrganizationUnitName AS OUName FROM OrganizationUnits ou ) TB1, (SELECT e.OrganizationUnitID AS OUId,SUM(trn.Amount) AS Amount FROM Employees e LEFT JOIN tblPeriodTransactions...
hibernate many to many mapping not inserting row into join table from service layer
hibernate,many-to-many,postgresql-9.1,jointable,hibernate-4.x
1 .dao i am using same do objects for saving/merging operations, where as in service layer, i have to map bo to do objects and now as per hibernate rule, no two objects will have same identifier, but in my case both bo and do object will have same identifier,...
JPA @JoinTable with extra join conditions
You can use @WhereJoinTable annotation. It applies to the association table @OneToMany @JoinTable( name="Contract_Party", joinColumns = {@JoinColumn(name="party_id",referencedColumnName="party_id")}, inverseJoinColumns = {@JoinColumn(name="contract_id", referencedColumnName="contract_id")} } @WhereJoinTable ( "ROLE = 'SIGNER' ") private List<Contract> contracts; ...
I can't drag fields from joint datasets into a grid or table while using BIRT
I figured out a solution to the problem. Not exactly a solution, more of a workaround. Right clicking on the field in the data set and clicking on "Insert in layout" inserts the field in the report and it can then be dragged to the desired position.
Linq to DataSet
c#,linq,linq-to-sql,jointable,asp.net-mvc-5.2
Sometimes it's easier to break it into multiple steps. First, get the collection of all boss IDs: var bossIDs = db.Departments.Select(x => x.BossId); Then get all sellers whose IDs are not in that collection: var listNonBoss = db.Sellers.Where(x => !bossIDs.Contains(x.Id)).ToList(); ...
How to POST/PATCH to a many-to-many relationship entity on an Azure Mobile Services?
entity-framework,automapper,azure-mobile-services,jointable,ef-fluent-api
I think PATCH should work, but I'm not an expert in automapper. Another option, if you only have a few users in each organization, is to create views in your backend that represent the joined tables. See my posts in this forum thread for an example of how this would...
SQL JOIN ON issue with aliases or not using aliases
You are mixing implicit joins and explicit joins. A simple rule: don't use commas in from clauses. SELECT h.hoursworked, p.projectname, p.description, p.archive, c.clientname FROM hours h join projects p on h.projectid = p.projectid join clients c ON p.clientid = c.clientid WHERE p.archive = 0; In addition, the syntax for using...
Yii Framework 2.0 get data from the left table which does not exist as foreign key in the right tabke
php,relational-database,yii2,jointable
The answer that arogachev posted is almost correct. If you were to run his method getOtherModels() you would get a 'Cannot convert object to string' exception. My method below does the exact same, except I have made the method static and added a foreach to loop through the objects and...
mysql join query on null value in the left table
Try this : SELECT * FROM user u left join location l ON u.location_id = l.location_id and l.status_id = 1 and l.location_type = 1 where u.status_id = 1 Here is the demo You don't need to use u.location_id IS NULL because left join does not exclude users, it just adds...
SQL One row that has multiple joins from different columns
sql,join,left-join,jointable,right-join
You need to give your table a different alias when you reference it the second time. Select m.Description, st.Description FROM BaseTable bt LEFT JOIN Mapping m where m.toID = bt.id LEFT JOIN BaseTable bt2 where m.FromID = bt2.id, inner join SubTypeTable stt on bt2.subTypeID = stt.id I personally would start...
Unable to get correct data over several joins
mysql,database,join,relational-database,jointable
The reason you are seeing attribute_id as null is because it is null inside the attributes_options table. This makes sense, because there is no option for those examples. To work around this, you should explicitly set the columns you want to select, from the tables where you would expect the...
SQL Product Group Size & Colors Together
Assuming that your tables are as follows: create table order (order_id int); insert into order values(2); create table order_product (order_id int, order_product_id int); insert into order_product values (2,7); insert into order_product values (2,8); create table order_product_attribute (orders_products_attributes_id int, order_id int, product_id int, products_options int, options_id int); insert into order_product_attribute values...
No such column for attribute in a join table
ruby-on-rails,activerecord,attributes,associations,jointable
The error is caused by a faulty foreign_key option in TaskVolunteer. belongs_to :volunteer, class_name: "User", foreign_key: :volunteer_id foreign_key here refers to the column on the users table not on tasks_volunteers. You can just remove the foreign key option. class TaskVolunteer < ActiveRecord::Base # task_id, volunteer_id, selected (boolean) belongs_to :task belongs_to...
Issue with Joint tables in mysql PHP
You can use aliases for this fields : SELECT authors.name , authors.id , authors.img , authors.slug AS 'authors.slug' , quotes.author_id , quotes.title , quotes.id , quotes.meta_keys , quotes.meta_description , quotes.slug AS 'quotes.slug' , quotes.content FROM quotes, authors WHERE quotes.author_id = authors.id ORDER BY RAND() LIMIT 1 So you can access...
MYSQL calculate amount from two table?
The fastest way is to union your data with the money value being negative on the collecting table: -- load test data create table orders(customer_id int, money int); insert into orders values (3,120), (5,80), (3,45), (3,70), (6,20); create table collecting(customer_id int,money int); insert into collecting values (3,50), (3,70), (4,20), (4,90);...
Permissions wont assign more then one?
ruby-on-rails,jointable,has-and-belongs-to-many
Your permission_names= method definition is the problem. In it, you keep setting self.permissions to an array with a single element. Try this instead. def permission_names=(names) self.permissions = Xaaron::Permission.where(name: names) end ...
How do I load a Set of Strings from a JoinTable?
hibernate,jpa,jpa-2.0,jointable
Use the @ElementCollection instead of the @ManyToMany, because @ManyToMany is between entities, and a String is not an Entity. Additionally replace @JoinTable with @CollectionTable(name="teacher_subj", [email protected](name="TEACHER_ID"))
BookshelfJs failing to bring in nested relationship on create
postgresql,express,jointable,bookshelf.js,knex.js
I'm not completely clear what you are trying to achieve, but here is how I would generally set things up. First I'd create a base model (assuming its saved as base.js), I think you are going to have some problems with circular dependencies, so using the Bookshelf registry plugin would...
MySQL table linking 1:n
This is usually done via third table: CREATE TABLE t_eventsusers ( eId INT(6), uId INT(6) ) ...
join multiple tables and fetch the status based on first table id
php,mysql,select,join,jointable
try this,. select a.agent_id, a.agent_name, a.company_name, ifnull(cnt_all,0) total_drivers,ifnull(cnt_active,0) active_drivers, ifnull(cnt_idle,0) idle_drivers from agent a left join (select agent_id, count(*) cnt_all from driver group by agent_id) cnt on a.agent_id=cnt.agent_id left join (select agent_id, count(*) cnt_idle from driver where last_viewed=0 group by agent_id) idle on a.agent_id=idle.agent_id left join (select agent_id, count(*) cnt_active...
Storing MySQL values as integers
Your sh_subscriptions table is actually a many-to-many join table that relates users to feeds. This is considered a fine way to design database schemas. Your basic concept is this: you have a collection of users and a collection of feeds. Each user can subscribe to zero or more feeds, and...
Join DB tables with hasMany association - store in array
The problem happens because of the type of join used. With inner join you'll get this return with your query profile_id location_id --------------------------- 1 2 1 3 And cake understands that as two records of Profile, so you get repeated Profiles with the same info. If this were all the...
Join five mysql tables for search
php,search,mysqli,sql-like,jointable
You join your table based on file_id in the following way.And you can also search by id.just putting your search id at Your_search_file_id place. select *from conference inner join journal on conference.file_id =journal.file_id inner join project_thesis on project_thesis.file_id =journal.file_id inner join research on research.file_id =journal.file_id where journal.file_id = Your_search_file_id; ...
JPA - @JoinColumn @ManyToOne to Class composed by PkClass
java,jpa,one-to-many,jointable
EDIT: Sorry my bad I missed the fact that you have extra columns in the Join table, so just need to decompose the ManyToMany association between ProductModel and LanguageCountry into two OneToMany associations. Your code should look like: in ProductModel class: @OneToMany private List<ProductModelI18n> ProductLanguages; In LanguageCountry class: @OneToMany private...
how to get third table count with group concat
mysql,mysqli,count,group-concat,jointable
You can try like below. You can get the concated user list and count from respective table and then can JOIN them with base product table to get the consolidated result. select p.*,XX.total_review,XXX.user_list from tbl_product p join ( select product_id, count(*) as total_review from tbl_product_review grup by product_id ) XX...
Displaying value from join table in Yii CgridView
yii,relation,jointable,cgridview
It means that in some conditions $data->student_skills is NULL. Try change this: 'value' => '$data->student_skills->value', to this 'value' => 'empty($data->student_skills) ? null : $data->student_skills->value', ...
Many to many MVC 5 Model Code first join table
ef-code-first,asp.net-mvc-5,many-to-many,entity-framework-5,jointable
You just need to alter the Team property on Player to: public virtual ICollection<Team> Teams { get; set; } Entity Framework will automatically discern an M2M exists and create a join table....
many to many association mass assign checkboxes ruby on rails 4
ruby-on-rails-4,checkbox,many-to-many,has-many-through,jointable
Models: class Course < ActiveRecord::Base has_and_belongs_to_many :enrollments class Enrollment < ActiveRecord::Base has_and_belongs_to_many :courses with this type of association you end up with a join table which will have course_id and enrollment_id . Thus not needing to create a new model just to handle your association. Rails already does that for...
update join table taking a very long time
The second row has value ALL in column type. This is the cause of the very, very slow execution. For each of the 5 million rows from citations it needs to scan all the 3 million rows of table pub_year in order to find the matching rows for the JOIN...
Rails 4 - find entity through many to many attribute
ruby-on-rails,ruby-on-rails-4,many-to-many,jointable
On Member define class Member belongs_to :user belongs_to :team def self.admin where(:admin => 1) end end Then you can write @team.members.admin which will give you the list of all admin members (not users). And then, you can add the following method on Team def is_admin(user) self.members.admin.where(:user_id => user.id).count == 1...
SQL query joining tables from diffrent fdb databases
sql,firebird,jointable,firebird2.1
You can't. In Firebird you can only join tables in the same database file. Firebird 2.5 expanded EXECUTE STATEMENT to also execute a statement on an external datasource, but having a single query reference tables in different databases is not possible. You have the following options: Create a temporary table,...