I'm trying to display all rows from one table and also SUM/AVG the results in one column, which is the result of a where clause. That probably doesn't make much sense, so let me explain. I need to display a report of all employees... SELECT Employees.Name, Employees.Extension FROM Employees; -------------- | Name | Ext | ------------...
I have 2 tables for which I need to run a query on Table1 has 2 fields: l_id, and name Table2 also has 2 fields: l_id, and b_id I need to run a query to get the "name" and "l_id" for all the entries in table1 that do not have an entry in table2 for a given b_id. Hope this makes some sense ...
Hello. I have two tables: 1. plan_payment_type: id plan_id 2. plan id payment_period It is Foreign key between plan_payment_type.plan_id and plan.id. In my code I know a $plan_payment_type_id. My task is to select payment_period. Steps: Select plan_id from plan_payment_type Select payment_period from plan with selected...
Hi, currently the stored proc returns one record based on a employee_number that is passed as a parameter. If the surpervisor_id is NULL, there is no record returned. If I want to return a record with all the other fields, and have a NULL in the supervisor_id field, how would I configure that? SELECT DISTINCT tblPeople.PERSON_ID,...
I have IEnumerable<A> and IEnumerable<B> I want to Group Join based on whether A.Test(B) returns true. The keyselector funcs do not seem to do this as the KeySelectors need to return keys of the same type so that they can be checked for equality. Is there something I'm missing here? ...
Hi Guys! I have two tables like this. Table 1 Fields: CId , Name Table 2 Fields: CId , food I want to get no. of food against each CId with the query "select * from table1" ...
I have this query: SELECT weekno, dt, SUM(CARRYOVER) AS CARRYOVER, daycode, COALESCE('N','N') AS FLAG, DATE_FORMAT(STR_TO_DATE(CONCAT(YEAR(dt), weekno, ' Sunday'), '%X%V %W') , '%c/%d/%Y' ) AS ENDOFTHEW...
how can i implement left outer join in following code: var showmenu = from pag in pagerepository.GetAllPages() join pgmt in pagerepository.GetAllPageMeta() on pag.int_PageId equals pgmt.int_PageId where (pag.int_OrganizationId == layoutrep.GetSidebarDetailById(SidebarDetailsId).int_Organizat...
For example I have a comments models, and I have a post model, but comments can comment on other comments. So it seems I need a join table I'll call commentables. To create this, do I really need to create a commentable table with a post_id and a comment_id ? Or can I do something like this without one : has_many :comments...
Hello. I have 3 tables. 1. payment(user_id, calculation_id) 2. user(id, user_name) 3. calculation(id, period_start_date, period_end_date) I need to select payments with user_name, period_start_date, period_end_date. How can I do it within one query in Zend Framework? Thanks a lot. ...
I have a task to optimize this query, any idea will be appreciated. SELECT DISTINCT `br`.`id` AS `branch_id` , `br`.`store_id` , `br`.`longitude` , `br`.`latitude` , `br`.`town_id` , `br`.`post_region` , `cb`.`cat0_id` , `cb`.`cat1_id` , `cb`.`cat2_id` , `plan`.`listing_pla...
I have a table with a column containing text, and I want to select all of the tables where the text from one of the rows in a second table appears in that row. Here is a pseudo-query: SELECT title FROM recipes AS r JOIN ingredients AS i ON r.ingredients LIKE '%' + i.name + '%'; The above query does not work, however. How do I ...
I'm using Fluent NHibernate in an attempt to improve testability and maintainability on a web application that is using a legacy database and I'm having some trouble mapping this structure properly: I have two tables that really represent one entity in the domain, and so I'm using a join to map them as such, and a third table that repre...
I have four tables, user, user_billingprofile, user_shippingprofile, and user_address. user: userId, dateCreated user_billingprofile: userId, address user_shippingprofile: userId, address user_address: random address crap Here is the query I have to get a users billing and shipping profiles in one shot. SELECT * FROM `user` JOIN `us...
I've got two datasources, one Oracle and one Sql Server. Due to circumstances that predate me (as in it was like this when I found it) some columns in the Oracle database contain PKs from lookup tables in the Sql Server database. I am attempting to create a Sql Server Reporting Services report that will combine data from both the Oracle...
p = Person.find_by_id(1, :include => :bags, :conditions => ['bag.id in (?), [3,4]) I would like to know how I could make sure this query will only be valid if both 'items.id' '3' & '4' are present rather than '3' or/and '4'. Thanks ...
Hi all, I'm using the following database: CREATE TABLE datas (d_id INTEGER PRIMARY KEY, name_id numeric, countdata numeric); INSERT INTO datas VALUES(1,1,20); //(NULL,1,20); INSERT INTO datas VALUES(2,1,47); //(NULL,1,47); INSERT INTO datas VALUES(3,2,36); //(NULL,2,36); INSERT INTO datas VALUES(4,2,58); //(NULL,2,58); INSERT INTO...
hi, I have a litte problem with a mysql query. I use 5 tables: user_has_data (uid, dataid); users (uid, uname); user_in_group(uid, groupid, data); groups(groupid, data, packageid); packages(packageid, name) all ids are PK. I want to build a sql query that finds a user, which belongs to a specified dataid, by its uname and checks if t...
I'm trying to realize the following query with zend db select: SELECT `uac`.`uid`, `u`.`uid`, `g`.`groupid`, `g`.`packageid` FROM `user_has_data` AS `uac` INNER JOIN `users` AS `u` ON u.uid = uac.uid LEFT JOIN (`user_in_group` AS `uig` INNER JOIN `groups` AS `ag` ON (ag.groupid = uig.groupid) AND (ag.packageid = 2) ) AS `g` ON u...
I have a query that retrieves some data, among those data I have some that are returned with a value like 0. I would like the query to NOT return the columns when that's the case. How can we do such a thing? Regards, MEM ...