w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Rails named scope for left joined model
if you want your query object to be chainable it must be an ActiveRelation object where, select, order and the other Arel objects return ActiveRelation objects that are chainable, so if the below works you can chain off of the returned query object note in rails 3 and up having a class method that returns an ActiveRelation is basically the same as a scope, they are both chainable query objects class Act def self.this_week_totals sunday = Time.now.beginning_of_week(:sunday).strftime("%Y-%m-%d") select("COALESCE(SUM(end_time - start_time), '0:00:00') AS total_time") .from("generate_series(0, 6) AS s(t)") .joins("LEFT JOIN acts ON acts.day = '#{sunday}'::date + s.t") .where("deleted_at IS NULL") .group("s.t") .order("s.t") end # ... end client

Categories : Ruby On Rails

MySQL Count Rows in Left-Joined Table
It should work (as it will count the non null values), but not sure how you would group the records to get it, seeing as you appear to be returning all the records from that table anyway. If you want a count that can be used for all rows, while bringing back the rows from t4 anyway you could do something like this:- $sql = "SELECT s.*, q.categories, t4.*, payments.*, quotes.*, t4_2.id_count FROM services s LEFT JOIN ( SELECT sc.service_id, GROUP_CONCAT(c.name) categories FROM services_categories sc JOIN categories c ON sc.cat_id= c.cat_id GROUP BY sc.service_id ) q ON s.service_id = q.service_id LEFT JOIN offers_to_purchase t4 ON s.service_id = t4.service_id LEFT OUTER JOIN ( SELECT service_id,

Categories : Mysql

How to access joined record from Rails LEFT OUTER JOIN
To eager load follows, you can call includes(): List.featured.includes(:follows).where(:follows => { :user_id => current_user.id }) It generates queries like this: SELECT "lists"."id" AS t0_r0, "lists"."is_featured" AS t0_r1, "lists"."created_at" AS t0_r2, "lists"."updated_at" AS t0_r3, "follows"."id" AS t1_r0, "follows"."followable_id" AS t1_r1, "follows"."followable_type" AS t1_r2, "follows"."user_id" AS t1_r3, "follows"."created_at" AS t1_r4, "follows"."updated_at" AS t1_r5 FROM "lists" LEFT OUTER JOIN "follows" ON "follows"."followable_id" = "lists"."id" AND "follows"."followable_type" = 'List' WHERE "lists"."is_featured" = 't' AND "follows"."user_id" = 1

Categories : Ruby On Rails

android Dividing circle into N equal parts and know the coordinates of each dividing point
You need to convert between polar and Cartesian coordinates. The angle you need is the angle between the (imaginary) vertical line that splits the circle in half and the line that connects the center with the circle's boundary. With this formula you can calculate the X and Y offsets from the center. In your example image the first angle is 0, and the second one is 360/n. Each next is i*(360/n) where i is the index of the current line you need to draw. Applying this will give you the X and Y offsets in a clockwise order (and you can just add them to the X and Y coordinates of the center to find the coordinates of each point) EDIT: some kind of pseudo-code: //x0, y0 - center's coordinates for(i = 1 to n) { angle = i * (360/n); point.x = x0 + r * cos(angle); point.y = y0 + r *

Categories : Android

SQL Server - Dividing one column by another in a SELECT query
Is this what you want? select (thisweek / lastweek) - 1 from (SELECT (CASE WHEN TIMESTAMP >= GETDATE() -7 AND TIMESTAMP < GETDATE() THEN ORDER_ID END) AS THISWEEK, (CASE WHEN TIMESTAMP >= GETDATE() -14 AND TIMESTAMP < GETDATE() -7 THEN ORDER_ID END) AS LASTWEEK from . . . ) t It defines the variables in a subquery and then ues them. Note that there is not any performance penalty for this; SQL Server does not "instantiate" the subquery.

Categories : SQL

SQL Query Across Multiple Joined Tables
Try this. SELECT res.id, res.name, ts.status FROM [Resource] res INNER JOIN Timesheet ts ON ts.resource_id = res.id LEFT JOIN TimePeriod tp ON ts.timeperiod_id = tp.id WHERE tp.week = '2013Week2' This is of course without creating the data locally. If this doesn't work let me know and I'll give it a second try and recreate your structure locally.

Categories : Mysql

joined query to return data
use LEFT JOIN instead of join select * from #__unis_faculties AS faculty join #__unis_subjects AS subject ON subject.faculty = faculty.id right join #__unis AS uni ON uni.id= subject.university where uni.id = 1

Categories : Mysql

Finding the most recent record in a joined query
Sure, here's a starting point. You need to get your contacts grouped by customer ID, so I'd start with this: SELECT MAX(contactDate), customerId FROM Contacts GROUP BY customerId From there, you can LEFT JOIN to your customers table, and you'll be able to see the last contact date against each customer. If you are sure that each customer has at least one contact, you can swap this for INNER JOIN, which should speed things up a bit.

Categories : PHP

How can I reduce the number of joined tables in this query?
Since the primary key key is ID, Type you can use aggregate functions and ensure the query is still deterministic, reducing the query to 0 joins: SELECT a.ID, MAX(CASE WHEN a.type = 'First Name' THEN a.Data END) AS FirstName, MAX(CASE WHEN a.type = 'Last Name' THEN a.Data END) AS LastName, MAX(CASE WHEN a.type = 'Timestamp' THEN a.Data END) AS `Timestamp`, MAX(CASE WHEN a.type = 'Count' THEN a.Data END) AS `Count`, MAX(CASE WHEN a.type = 'MY_ID' THEN a.Data END) AS MY_ID, MAX(CASE WHEN a.Type = 'Foreign Id' THEN a.Data END) AS ForeignId FROM Attributes a GROUP BY a.ID; It is worth noting though that the Entity-Attribute-Value Model is an SQL Antipattern, and you could be much better off normalising your data to store the attributes as

Categories : Mysql

Input with joined query component without JavaScript
What exactly are you posting the form too? What language are you using? I guess I don't understand fully what you are trying to do, because both of these examples: http://example.com/page.html?a=constant+QUERY_TEXT http://example.com/page.html?a=constant&b=QUERY_TEXT Could work, and be parsed by whatever language is receiving the form. It would only take a bit of logic to deconstruct the string. I've done something similar using jQuery and Ajax for my form submissions. I have a simple button that holds several key parameters like so: <a class="alertDeleteButton" href="https://www.example.com/index.cfm?controller=hobbygroupmember&amp;action=deletemember&amp;deleteuserid=#userid#&amp;hobbyid=#id#&amp;adminid=#user.key()#" id="alertDeleteButton_#id#_#userid#" tit

Categories : HTML

Filtering the MAX() function in a JOINED MySQL Query
What about adding the statement to where clause: $pieces['where'] .= " WHERE p.post_status='publish' GROUP BY t.term_id"; . Unless i missed something this should be an easy fix. Hope this helps.

Categories : PHP

SQL query to return difference between table joined to itself
select distinct PortfolioGroupCode, MemberID, MemberCode from test where memberID not in ( select distinct a.memberID from test a, test b where a.memberId = b.memberID and a.PortfolioGroupCode in ('open','master') and a.PortfolioGroupCode <> b.PortfolioGroupCode) and PortfolioGroupCode in ('open','master') Here is the DEMO

Categories : SQL

Slow Query when Joined View contains PIVOT
After you provided more information, I suggest you to try following. On the table tblPhoneNumber add index: create index ix_tblPhoneNumber_1 on tblPhoneNumber (isDeleted, ObjectKey, ObjectTypeName, Preference) include (PhoneNumber, PhoneTypeName) On the table tblPhoneType add (if there are no) either create unique clustered index ix_tblPhoneType on tblPhoneType (PhoneTypeName) or create unique index ix_tblPhoneType on tblPhoneType (PhoneTypeName) include (Mask) Change your vwPhoneNumberMasked view as: ALTER VIEW [vwPhoneNumberMasked] AS SELECT ph.ObjectTypeName, ph.ObjectKey, mp.Value as MaskedNumber, ph.Preference FROM tblPhoneNumber ph inner /*loop*/ join tblPhoneType pt on ph.PhoneTypeName = pt.Name cross apply ftMaskPhone(ph.PhoneNumber, pt.Mask) m

Categories : SQL

Oracle SQL Query to get missing date from two joined tables
If I understand the requirement, this should do the trick: SELECT d.ID, d.Device_Number, d.Customer_ID, d.Model FROM Device d LEFT JOIN Profile p ON d.Customer_ID = p.Customer_ID WHERE d.Model = 'UNITA' AND (p.ID IS NULL OR p.Type = 'TEST') It works because of the LEFT JOIN, which will make Profile.ID NULL if there's not a matching Profile row for the Customer_ID. If there is a matching row, the test for Profile.Type = 'TEST' will determine what's included. There's a SQL Fiddle here. The Fiddle includes the Profile.ID and Profile.Type values in the results because I think they help explain things more clearly. Addendum: Some confusion on my part over the requirements; this query may be closer to what's needed: SELECT d.ID, d.Device_Number, d.Customer_ID, d.Model, p.id AS pid, p.t

Categories : Oracle

Query performance optimization for dynamically joined columns
One of the fundamental flaws in an Entity Attribute Value design (which is what you have here) is the difficulty of efficient and performant querying. The more efficient structure for storing data is to abandon EAV and use a normalised relational form. But that will necessarily involve changing the structure of the database when the data structures change (which should be self evident). You could abandon your TextValue/NumericValue/DateValue/BoolValue fields and replace them with a single sql_variant column, which would reduce your query complexity slightly, but the fundamental problem will remain. As a side note, storing all numerics as floats will cause problems if you ever have to deal with money.

Categories : SQL

How to show zero when no result is returned in grouped joined query
Use an outer join and IFNULL() instead of an inner join. SELECT AVG(c.obj_rating) AS avg_rating IFNULL(i.fk_obj_id, 0) AS has_images, /* ... */ FROM obj_comments c LEFT JOIN obj_images i ON c.fk_obj_id = i.fk_obj_id GROUP BY c.fk_obj_id ORDER BY c.comment_id DESC LIMIT 10 Also I think declaring table aliases is a good idea.

Categories : Mysql

mysql query to find overlap in joined tables
SELECT COUNT(DISTINCT profiles.id) AS num FROM profiles LEFT JOIN invoices ON (profiles.id=invoices.profileid) AND (invoices.paid='Paid' OR invoices.paid='Unpaid') WHERE IFNULL(invoices.paid, '') LIKE '$paid%' OR IFNULL(invoices.paid, '') LIKE '$unpaid%' But a whole different thing is that you shouldn't use interpolated variables (like $paid above) and I find it odd that you have to use LIKE '[...]%' to select the right value. It looks as if the database schema is in a bad shape.

Categories : PHP

Perform a query with aggregation and grouping by joined tables
Just do SELECT 'DOMESTIC_CAT', c.color, count(*) FROM domestic_cat d INNER JOIN cat c ON c.id = d.cat_id GROUP BY c.color UNION ALL SELECT 'PERSIAN_CAT' .... the same for the other table

Categories : SQL

Linq query with Include to count joined records
Try this. This gets the date from StartDate by using the .Date property. And it names the result of the Count() call as "StudentCount". select new { ev.EventID, ev.StartDate.Date, ev.Title, StudentCount = ev.EventStudents.Count() }

Categories : C#

Cake PHP joined query returned as associative array
Please try $findParameters = array( 'conditions' => array( 'Account.client_id' => $clientId, 'Holding.holding_date = LAST_DAY(Holding.holding_date)', //gets month-end dates only 'MONTH(Holding.holding_date)' => array(3,6,9,12) //Use for quarter-end dates ), 'fields' => array( 'Holding.holding_date', 'SUM(Holding.value) AS portfolio_value' ), 'group' => array('Holding.holding_date'), 'contain' => array('Account), );

Categories : PHP

linq query with dynamic predicates in where clause joined by OR
Thanks to Raphaël Althaus that gave the following link: http://www.albahari.com/nutshell/predicatebuilder.aspx Predicate builder is the solution. You can use it installing LinqKit from Nuget. In that url you can find also the implementation of this class. Note: in order to make this work with LinqToSql or LinqToEntities the IQueriable Object must be transformed using "AsExpandable()" method, for memory objects it's not required

Categories : Linq

Cypher query to retrieve indexed relationships and joined nodes
Oddly enough, this gets what I want: START source=node(*) MATCH source-[relationship]->target WHERE relationship.deleted = "false" RETURN source, relationship, target; It seems less efficient. Any insight that anyone can provide would be appreciated.

Categories : Neo4j

mysql_query returns true when executing joined UPDATE query
I've read the documentation on PHP.net and learned when using INSERT, UPDATE, DELETE, DROP the method returns true or false When using SELECT, SHOW, DESCRIBE, EXPLAIN you get an resource when the query is successful and false when the query failed. Thanks for the comments guys.

Categories : PHP

PostgreSQL partitioning with joined table - partition constraint not used in query plan
This scheme just isn't going to work. constraint_exclusion is simple and dumb. It must be able to prove by examining a query during planning that the query cannot touch certain partitions in order to exclude them. Excluding partitions during query execution isn't supported at this time. There's a lot of room for improvement in the rudimentary partitioning support Pg offers, and execution-time constraint exclusion is just one of the areas that could use work. Your application will need to know about the partitions and their constraints, and will need to explicitly join on the union of only the partition(s) that are required. In this case I'm not sure how PostgreSQL could even do what you want. I guess you want it to project the constraint through the composite key on the join, asserting

Categories : SQL

MySQL join query with only one record from joined table, ordered by date
i think this will do it: SELECT t.*,d.* FROM opc_tag t LEFT JOIN opc_data d ON t.id = d.tag_id WHERE d.created_at = ( SELECT MAX(created_at) FROM opc_data WHERE tag_id = t.id ) GROUP BY t.id if you want to show all tags, even those that have no data, use this in the where clause: WHERE d.created_at = ( SELECT MAX(created_at) FROM opc_data WHERE tag_id = t.id ) OR d.created_at IS NULL

Categories : Mysql

optimizing left join query or creating an equivalent mysql query
try this SELECT t1.id, t2.hash_id, t2.name , t2.id as id2 FROM table1 as t1 LEFT JOIN table2 t2 ON t1.table2_id = t2.id WHERE t1.id IN ('1','3') FIDDLE

Categories : Mysql

Parameterized query binding in ON clause for a LEFT JOIN in Laravel Eloquent / Query Builder
This comes straight from the Laravel docs: The Laravel query builder uses PDO parameter binding throughout to protect your application against SQL injection attacks. There is no need to clean strings being passed as bindings. You shouldn't need to sanitize it at all. It should be fine. If you are worried about it though, you can use the Validator class to validate it however you want.

Categories : PHP

Laravel 4 Query Builder: LEFT JOIN ... AND ... query
In Laravel 4 you can do this using Eloquent query. I assume that you are aware of Eloquent relationships and know to to create models. For your query it should be RunUser::where('user_id',$id)->leftjoin('runs','runs.id','=','run_user.run_id')->first(); Note RunUser is the model for run_user table.

Categories : PHP

How can I create sub query i have already LEFT JOIN in my first query
In your FROM cluase, you have defined alias t1 for imaster_tbl as FROM imaster_tbl t1 and in the query you are using imaster_tbl to refer to this table/view. You should use the alias name once you declare the alias.

Categories : PHP

CSS (Fixed width right, fluid left, left is first in html) How do I keep left div from collapsing?
Use display:table for the parent container and display:table-cell for the two columns within. The one first in the DOM will be to the left and the next will be to the right. Set fixed width for whichever you want to , and auto width for the other and it will fill up remaining space. Here's a fiddle - http://jsfiddle.net/ZVP6A/26/

Categories : CSS

How can i have INNER and LEFT JOIN in the same query?
An earlier answer, since deleted, recommended you remove all the parentheses from your query. However Access requires parentheses in the FROM clause when it includes more than one join. Since the problem is with the joins, start with a simpler query which focuses on them only. See whether this query runs without error. SELECT * FROM (T2 INNER JOIN TB ON T2.Col1 = TB.Col1 AND T2.Col2 = TB.Col2) LEFT JOIN T1 ON T2.Col1 = T1.Col1 AND T2.Col2 = T1.Col2 Once you get the joins correct, replace * with your field names and add your WHERE clause.

Categories : SQL

Need SQL aid for query with LEFT JOIN
Are you looking for this? SELECT s.*, c.country, c.clientname FROM server s LEFT JOIN client c ON s.clientid = c.clientid Here is SQLFiddle demo. For better understanding of JOINs see A Visual Explanation of SQL Joins

Categories : Mysql

SQL query LEFT JOIN
SELECT p.Product_Line, p.Product_Code,COUNT(*) as OpenedToday FROM SFCase as c RIGHT OUTER JOIN (SELECT DISTINCT Product_Code from SFCase) as p ON p.Product_Code = c.Product_Code GROUP BY p.Product_Line, p.Product_Code

Categories : SQL

SQL Using inner query in left join
You can do this with a join and aggregation (if I understand the logic correctly): select ONE.id, max(TWO.CODE) from ONE left outer join TWO on ONE.id = TWO.id group by ONE.id;

Categories : Mysql

Multiple LIKE's in query with 2 LEFT JOINS
Assuming you are interested in items which satisfy atleast one LIKE condition, you need to replace ANDs with ORs $STH = $DBH->query("SELECT * FROM artikel LEFT JOIN barcode ON artikel.barcode_id = barcode.barcode_id LEFT JOIN debiteur ON artikel.debiteur_id = debiteur.debiteur_id WHERE debiteur.debiteur_naam LIKE '%$formulier%' OR artikel.artikel_leverancier LIKE '%$leverancier%' OR artikel.artikel_leverancier_code LIKE '%$artikelleverancier%' OR artikel.artikel_code LIKE '%$artikelexact%' OR artikel.artikel_omschrijving LIKE '%$artikelomschrijving%' ORDER BY '$orderby' ASC");

Categories : PHP

Left Join query with where clause
Your where clause is undoing the left join. When there is no match, start_date is NULL, which fails the comparison. Moving the condition to the on clause fixes this: SELECT Person.id, Person.firstName, Workout.duration FROM Person LEFT OUTER JOIN Workout ON Person.id = Workout.person_id and Workout.start_date BETWEEN %s AND %s where Person.group_id = %s

Categories : SQL

Sub query filter in LEFT/RIGHT JOIN
Here is your query, written so I understand it better: select * from tab2 t2 left outer join tab1 t1 on t1.MemNo = t2.MemId where exists (SELECT 1 FROM TAB3 x WHERE x.Col2 = T2.SVID AND x.Col1 = T1.SID ) and T1.MemNo IS NULL The column t1.MemNo is NULL only when the outer join fails to find a match in that table (because it is part of the join condition). When this is NULL then so is T1.SID, so the exists clause will find no rows because one of the AND conditions evaluates to NULL. If you want to handle this case, then you need logic in the exists clause as well: select * from tab2 t2 left outer join tab1 t1 on t1.MemNo = t2.MemId where exists (SELECT 1 FROM TAB3 x WHERE x.Col2 = T2.SVID AND (x.Col1

Categories : SQL

Error when using left join with my query
Let me break it down for you. This example returns the exact same error, because you're selecting from not-a-subquery: select * from ( -- missing a select here! -- select * from --uncomment this line ( select 1 as x ) a left join ( select 2 as y ) b on a.x=b.y ) c

Categories : SQL

Query duplications on left join
why not join on one condition and filter data in where condition :- $query_pag_data = "SELECT m.date, m.points, m.time, m.location, m.epos_id, m.rbpos_id, m.time_added, r.epos_id, r.rbpos_id, r.location FROM matching as m LEFT JOIN rbpos_epos as r ON m.epos_id = r.epos_id WHERE m.rbpos_id = r.rbpos_id AND m.user_id = ".$id_user." ORDER BY m.time_added DESC LIMIT ".$start.", ".$per_page;

Categories : PHP

need a help in left join query in mysql
You need to move the is_deleted check to the JOIN: select t1.name, t1.map_id, t1.reg_id, -- replace select * with columns t2.map_name, t2.is_deleted from table1 t1 left join table2 t2 on t1.map_id = t2.map_id and t2.is_deleted = 0 WHERE t1.reg_id = 5; See SQL Fiddle with Demo. Your current query is causing the LEFT JOIN to act like an INNER JOIN because you have the is_deleted as a part of the WHERE clause. If you move that to the JOIN, then you will return the rows from table2 that have is_deleted=0 and you will still return all rows from table1 where reg_id=5.

Categories : Mysql



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.