w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
SQL query to select distinct rows from left table after inner join to the right table
your query should be like $query = " select t1.id, t1.title, t1.description, group_concat(t2.size SEPARATOR ",") as sizes from products as t1 inner join sizes as t2 on t1.id=t2.id where t1.id in (select t3.id from sizes as t3 where t3.size in (".$size_list .") group by t1.id, t1.title, t1.description " a bit of explanation. When you join two tables, you get all rows from table sizes for all id from table products, so id = 1 joined with 4 records and id = 2 joined with 2 records. So you have to aggregate this numbers into one record.

Categories : Mysql

SQL: JOIN two tables with distinct rows from one table
You can do something like this: In first CTE - assign rankings for VirtualLicenses within the Product groups. In second CTE - assign rankings for Licensce within the Product groups (order by exp. date) And at the end just join the two subqueries on productID and ranking. WITH CTE_VL AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY ProductId ORDER BY vl.VirtualLicenseId ASC) RN FROM dbo.VirtualLicense vl LEFT JOIN dbo.Product p ON vl.ProductName = p.Name ) ,CTE_License AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY ProductId ORDER BY ExpiryDate ASC) RN FROM dbo.License ) SELECT VirtualLicenseId, LicenseId FROM CTE_VL vl LEFT JOIN CTE_License l ON vl.ProductId = l.ProductID AND vl.RN = l.RN SQLFiddle DEMO

Categories : SQL

How to select distinct pairs in MySQL join (same table) with transitivity?
Group on only one side and take the MIN() of the other: SELECT MIN(a.ID) a, b.ID b FROM mytable a JOIN mytable b ON b.text = a.text AND b.ID > a.ID GROUP BY b.ID See it on sqlfiddle.

Categories : Mysql

Join Distinct Id on non-distinct id (MySql)
SELECT t.ID, t.val_string, t.val_int, t.val_datetime FROM table1 AS t LEFT JOIN (subquery) AS v_table ON t.ID = v_table.ID Sample fiddle

Categories : Mysql

SQL Insert rows into table that must have 2 distinct columns but also one non distinct column
You'll have to just replace the PRIZEID value with new ones. Because it sounds like you currently have duplicates on your PROMOTION table First add all the distinct PRIZENAMEs and COSTs to your new PRIZE table: INSERT INTO prize(PRIZEID, COST, PRIZENAME) SELECT DISTINCT (SELECT MAX(PRIZEID)+1 FROM PRIZE), r.COST, r.PRIZENAME FROM PROMOTION r; Then update your PROMOTIONs table with the new PRIZEID UPDATE PROMOTION R SET R.PRIZEID = (SELECT P.PRIZEID FROM PRIZE WHERE P.PRIZENAME=R.PRIZENAME AND P.COST=R.COST); Then, I think from there you can safely delete the columns from your PROMOTIONs table

Categories : SQL

SQL INNER JOIN DISTINCT RESULT
Well, the query is returning distinct results. If you were refering to EmployeeId, NAME and Department, then you could try something like SELECT DISTINCT s.EmployeeId, e.NAME, e. Department FROM SelfAnswers AS s Inner Join Employee As e ON s.EmployeeId=e.EmployeeId

Categories : Sql Server

MySQL LEFT JOIN with DISTINCT or LIMIT. Need to LEFT JOIN only one value
Try something like this: SELECT TransactionPartnerName, Amount FROM 18_8_ChartOfAccounts LEFT JOIN 2_1_journal ON AccountNumber = DebitAccount LEFT JOIN 18_6_TransactionPartners ON TransactionPartnerName = CompanyName WHERE VatReturnRowNumberForDebitTurnover = '61' AND Amount = 55 GROUP BY TransactionPartnerName;

Categories : Mysql

MYSQL JOIN and GROUP / DISTINCT
If you use WHERE instead of HAVING you would be able to use GROUP BY / DISTINCT and catch 'm all like so: SELECT u.id AS USERID FROM `GEO` g JOIN `LOCATIONS` l ON l.`AREA` = g.`ID` JOIN `USER` u ON l.`ID` = u.`ID` WHERE (6371 * ACOS(COS(RADIANS({$lat})) * COS(RADIANS(g.latitude)) * COS(RADIANS(g.longitude) - RADIANS({$long})) + SIN(RADIANS({$lat})) * SIN(RADIANS(g.latitude)))) <= {$radius} GROUP BY u.`ID` This may be optimized by using an 'early' pre-aggregated filter. I.e. by applying the WHERE on the ON as early as possible. Though this may look 'weird', it can be significantly faster. In your case this would look like this: SELECT u.id AS USERID FROM `GEO` g JOIN `LOCATIONS` l ON (6371 * ACOS(COS(RADIANS({$lat})) * COS(RADIANS(g.latitude)) * C

Categories : PHP

Distinct left join for foreign id
You can use SQL with subquery, like: SELECT messages.*, max_stamps.max_stamp FROM (SELECT MAX(timestamp) as max_stamp, `from` FROM messages WHERE messages.`to`=$currentUser GROUP BY `from` ) as max_stamps LEFT JOIN messages ON messages.`from`=max_stamps.`from` AND max_stamps.max_stamp=messages.timestamp -i.e. select max timestamps first in subquery, then use them for main table. (update) If you want get maximum id value, then use it in MAX(): SELECT messages.*, max_ids.max_id FROM (SELECT MAX(id) as max_id, `from` FROM messages WHERE messages.`to`=$currentUser GROUP BY `from` ) as max_ids LEFT JOIN messages ON messages.`from`=max_ids.`from` edit: misunderstood id, f

Categories : PHP

mysql distinct results after join
Move all the nvdproducts checks into a subquery, so you can use SELECT DISTINCT there to prevent duplication. SELECT DATE_FORMAT(published,'%Y-%m'), severity, COUNT(severity) FROM nvdcve LEFT JOIN (SELECT distinct cve_id FROM nvdproducts WHERE (nvdproducts.company='linux' and nvdproducts.product='linux_kernel' AND nvdproducts.version IN (SELECT Kernel from Versions.VIEW_kernel)) OR (nvdproducts.company='apache' and nvdproducts.product='http_server' AND nvdproducts.version IN (SELECT Httpd from Versions.VIEW_httpd)) OR (nvdproducts.company='sendmail' and nvdproducts.product='sendmail' AND nvdproducts.version IN (SELECT Sendmail from Versions.VIEW_sendmail)) OR (nvdproducts.company='mysql' and nvdproducts.product='mys

Categories : Mysql

SQL Query Distinct Inner Join Issue
Possibly: select * from (SELECT DISTINCT Name from Names WHERE Haircolor='Red') uniquenames INNER JOIN (SELECT * FROM Names) allnames ON uniquenames.Name = allnames.Name - it depends on what you actually want to see. UPDATE: SQLFiddle for original answer here. However, a better answer (if using MySQL) might be: select * from Names WHERE Haircolor='Red' group by Name (Note that the specific row returned for each Name matching the hair colour is essentially random.) SQLFiddle here. Further Update: For SQLServer, try: with cte as (select n.*, row_number() over (partition by name order by address) r from Names n WHERE Haircolor='Red') select NAME, HAIRCOLOR, ADDRESS, PHONE, EMAIL from cte where r=1 - this will return the first row (by address, alphabetically) for each name - SQL

Categories : SQL

how to select distinct columns in join querys
This selects the distinct values. Is it what you want? SELECT distinct Tracking.GTIM FROM Tracking INNER JOIN Vehicles_Device_Rel ON Tracking.M = Vehicles_Device_Rel.Device_ID WHERE (Vehicles_Device_Rel.VehicalNumber = 'test')

Categories : SQL

DISTINCT is not working in join query Cakephp
DISTINCT in SQL Server applies to the entire row you are returning from your result set. So if your data contains, for example: mobileNo workNo homeNo name ... 1234 1234 5678 Joe ... 1234 867 5309 Stacy ... 1 555 5555 Sentinel ... Your DISTINCT will not remove Joe or Stacy, because the entire rows are not equal. On the other hand, if your data contained: mobileNo workNo homeNo name ... 1234 1234 5678 Joe ... 1234 1234 5678 Joe ... 1234 1234 5678 Joe ... 1234 1234 5678 Joe ... 1234 1234 5678 Joe ... 1234 867 5309 Stacy ... 1 555 5555 Sentinel ... Your query would return only a single entry with the name "Joe", because it would return only

Categories : Cakephp

Get Distinct rows from a result of JOIN in SQL Server
You are getting duplicate because there are multiple photos per album. To get one, use row_number(): SELECT Album_Name AS Album_Name, a.Album_Date AS Album_Date, a.Page_ID AS PageID, p.Image_ID AS Image_ID, p.Image_Small AS Image_Small FROM Album_Name a left outer JOIN (select p.*, row_number() over (partition by Album_Id order by Image_ID) as seqnum from Album_Photos p ) p ON a.Album_ID = p.Album_ID and seqnum = 1;

Categories : SQL

Join two tables and show distinct records
As you are joining on the PART_ID column, the result you are getting is what you would expect to get. For example, there are two C0001 in the LOG-OUT table and these will join to the same C0001 in the PART RETURN table as there is nothing to distringuish the rows in the return table from each other: PART LOG-OUT TABLE -> PART RETURN TABLE C0001 07/29/2013 501 -> C0001 09/04/2013 STORE C0001 10/01/2013 605 -> C0001 09/04/2013 STORE You need another criteria to join on, or you need to join on some more unique identifier in order to achieve the results you are looking for.

Categories : Ms Access

Join two tables and display distinct list of column
A join will do: SELECT s.skill_name, CASE WHEN c.candidate_id IS NOT NULL THEN '(X)' ELSE '()' END has_skill FROM skills s LEFT JOIN CandidateToSkillMap c ON s.skill_id = c.skill_id WHERE c.candidate_id = 1 If it joins the right side successfully and thus c.candidate_id will be not null, then it means the person has that skill and you mark it else - don't.

Categories : PHP

How to select only distinct result based on particular creteria from tables using join
This should help get you on your way. You need to use a where clause. SELECT a.callnumber, b.callnumber FROM table a INNER JOIN table b ON a.callnumber=b.callnumber WHERE a.callduration > value AND b.callduration > value.

Categories : Mysql

MySQL - Taking DISTINCT values from table A and turning into PRIMARY KEY on table B
If you are only trying to insert new values, this should work. You could make a left join to the target table and only include records that don't exist (WHERE pbu.ProductID IS NULL). INSERT INTO ProductDescriptionsbackup(ProductID) SELECT DISTINCT p.`PRODUCT CODE` FROM Products as p LEFT JOIN ProductDescriptionsbackup as pbu ON p.`PRODUCT CODE` = pbu.ProductID WHERE pbu.ProductID IS NULL;

Categories : Mysql

xslt 1.0 - display only one distinct value in a table cell as part of a multi-column table and alternate colors
For the colours you essentially have two cases depending on whether $rowLabs is odd or even. If it's even then odd rows will be entirely "white" and even rows will be entirely "black" If it's odd then for odd rows, odd columns will be white and even ones black for even rows, odd columns will be black and even ones white ("odd" and "even" counting from 1 as XPath position() does, so the first row/column is odd, the second is even, etc.). You can encode that logic in XSLT by adding some parameters to the templates. Replace the <xsl:apply-templates select="self::*|$otherEntries" /> with <xsl:apply-templates select="self::*|$otherEntries"> <xsl:with-param name="rowNum" select="position()" /> <xsl:with-param name="totalRows" select="$rowLabs" /> <

Categories : Xslt

Join two table show all record on left table if right table exists, use right table
you can use Full outer join here... here is an example SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID

Categories : SQL

Join Table on one record but calculate field based on other rows in the join
You can set a variable using a condition: select a.*,(DATEDIFF(D,s.SENDDATE,GETDATE()) >= 7) as `IncentiveRecieved ` from AbandonCart_Subscribers a left join SendLog s on a.EmailAddress = s.EmailAddress and s.CampaignID IS NULL where DATEDIFF(day,a.DateAbandoned,GETDATE()) <= 1 Is this what you're looking for?

Categories : SQL

SQL server join selects slower than join select with local table
Your two queries are not the same. The first is storing part of the results in a table variable. The second is including it in the query. The execution plans are almost necessarily different. To understand the differences, you need to look at the query plan. Here are some possible reasons why the performance might be different from what you expect: Perhaps the overhead of returning rows from the database is simply greater than the overhead of inserting into a table. The SQL query may have more information about the table variable when it compiles, because the table variable is already created when the second part is compiled. The combined query may just choose a poor execution plan, even if all the statistics are updated. That is, doing the "P2" query together may be optimal, but i

Categories : SQL

How to make a fancy mysql join that can join three tables and detect if one table DOESNT have my item
I think this is a left outer join problem Have a look at this example. You specifically need to have a check for a null in a column in the table which you want to find the missing row rof mysql left outer join

Categories : PHP

select all of table A left join column of table B with multiple wheres that dont affect showing all of table A
I think you just need to move the condition on the work_orders_billing_codes table into the on clause. Then if there is no match, you will still get all the appropraite records in billing_codes. SELECT bc.`id` AS `id`, bc.`billing_code` AS `billing_code`, bc.`desc` AS `desc`, bc.`point_value` AS `point_value`, wobc.`quantity` AS `quantity`, wobc.`billing_code_id` AS `id2` FROM `cl_billing_codes` bc LEFT JOIN `cl_work_orders_billing_codes` wobc ON bc.id = wobc.`billing_code_id` AND wobc.`work_order_id` = 1585 WHERE bc.`service` = '1' ORDER BY `billing_code`;

Categories : PHP

How can i join two table in Linq and display the join to Data GridView?
You are almost there. You are just missing .ToList() var teacherGroup = (from t in context.Teachers join g in context.Groups on t.ID equals g.TeacherID where num == t.ID select t).ToList(); teachergrpGridView.DataSource = teacherGroup;

Categories : C#

MySQL join update: Updating join table row twice
You need to use GROUP BY here. But we can't use it directly. So you can achieve this using sub-query like this: UPDATE OrderRows o JOIN Stock s ON s.ProductNumber = o.ProductNumber JOIN ( SELECT ProductNumber, SUM(Quantity) AS Quantity FROM OrderRows WHERE Done = 'no' GROUP BY ProductNumber ) o1 ON o.ProductNumber = o1.ProductNumber SET o.Done = 'Yes' ,s.Quantity = s.Quantity - o1.Quantity WHERE o.Done = 'no'; Tables after update: OrderRows | ID | ORDERNUMBER | PRODUCTNUMBER | QUANTITY | DONE | ------------------------------------------------------ |  1 |          10 |           100 |        1 |  Yes | |  2 |          10 |           101 |        1 |  Yes | |  3 |          10 |    

Categories : Mysql

Left Join table only if another join passes some criteria
Your version 1 doesn't need to be a sub query, you just need () around the INNER JOIN section... SELECT A.* FROM TableA A LEFT JOIN ( TableB B INNER JOIN TableC C ON B.CID = C.CID AND C.PassesTest ) ON B.BID = A.BID I've also put what you have as a WHERE clause in the INNER JOIN predicate. Also, be aware that in neither your sub-query version nor this example, does the entire of TableB necessarily get joined to TableC before being joined to TableA. SQL is compiled to an execution plan, and the optimiser has many options to prevent this from happening. Just because you have it written like that doesn't mean that the RDBMS will follow it blindly without optimising.

Categories : SQL

Table Join in JPA - Creating an entity that contains Join
You create a Person instance and persist it. JPA inserts a row in the person table. Then you create an Address instance and persist it. JPA inserts a row in the address table. Then you add the address to the list of addresses of the person, and Hibernate inserts a row in the join table. This is in fact partially true, because the inserts will in fact be done when you commit the transaction. But the principle of an ORM like JPA is that the mapping you specify in the entities tell JPA how and where the entities and their associations must be persisted.

Categories : Java

sum of distinct value count from one table
if you have known number of degree, this could be done using static query, SELECT Name, SUM(CASE WHEN degree = 11 THEN 1 ELSE 0 END) `degree 11`, SUM(CASE WHEN degree = 12 THEN 1 ELSE 0 END) `degree 12`, SUM(CASE WHEN degree = 13 THEN 1 ELSE 0 END) `degree 13` FROM tableName GROUP BY Name SQLFiddle Demo OUTPUT ╔════════╦═══════════╦═══════════╦═══════════╗ ║ NAME ║ DEGREE 11 ║ DEGREE 12 ║ DEGREE 13 ║ ╠════════╬═══════════╬═══════════╬═══════════╣ ║ Adam ║ 2 ║ 1 ║ 0 ║ ║ Beny ║ 1 ║ 0 ║ 1 ║

Categories : Mysql

Get distinct values from table
Maybe you need to restart your rails server: https://github.com/rails/rails/issues/7399 Previous similar SO question Update: From the docs: When using pluck, "The values has same data type as column" Change loop to: <% @count.each %> do |i| <%= i %> <% end %>

Categories : Ruby On Rails

Counting Distinct values from Sql Table
You would add a where clause into the subquery: SELECT Userid, (case when [YES] is null then 0 else [YES] end) as Completed, (case when [NO] is null then 0 else [NO] end) as Pending, (case when [YES] is null then 0 else [YES] end + case when [NO] is null then 0 else [NO] end ) as Total FROM (SELECT Userid, SurveyStatus, COUNT(ParcelId) as cnt FROM ParcelAllocationsurvivor WHERE DateAllocated >= '2013-08-01' and DateAllocated <='2013-08-07' and SurveyType = 'Survey1' group by Userid, SurveyStatus ) AS p PIVOT(max(cnt) FOR surveystatus IN([YES],[NO])) AS pvt order by Userid; By the way, the select distinct are redundant. You do not need them for this query.

Categories : Mysql

Select distinct Categories from a table
I am not at all sure which solution you would prefer (SQL, VBA, Excel formula) but I think a 'spreadsheet' one is:

Categories : SQL

UPDATE Table with Distinct IDs but Same DisplayNum
Did I get it right that you want to update the record with the lowest ID that still satisfies your WHERE condition? Try UPDATE tblData SET Called = GETDATE() WHERE DisplayNum = @DisplayNo AND Called IS NULL AND ID = (SELECT TOP 1 ID FROM tblData WHERE DisplayNum = 'A1' AND Called IS NULL ORDER BY ID ASC)

Categories : Sql Server

How to take distinct value in SQLite joined table?
I think you want to eliminate the duplicates by using GROUP BY. Like this: query = "SELECT" + " e.id AS _id," + " ke.id AS ke_id," + " ke.fk as ke_fk," + " ke.value as ke_value," + " re.id AS ke_id," + " re.fk as re_fk," + " re.value as re_value," + " s.id AS s_id," + " s.fk as s_fk," + " g.id AS g_id," + " g.fk as g_fk," + " g.lang," + " g.value as g_value" + " FROM entry e" + " INNER JOIN k_ele ke ON e.id = ke.fk" + " INNER JOIN r_ele re ON e.id = re.fk" + " INNER JOIN sense s ON e.id = s.fk" + " INNER JOIN gloss g ON s.id = g.fk" + " WHERE g.va

Categories : Android

select distinct a.* from table query
If table tbl_applicant exists No, it's only eliminate dupliacte rows. select col1,col2 ... from tbl_applicant group by col1,col2 ... or with alias a select a.col1, a.col2 ... from tbl_applicant a group by a.col1,a.col2 ...

Categories : SQL

Does 'select distinct' returns the first distinct value or last distinct?
I think there's a misunderstanding here: Your query does not return the records, only the distinct column values. Which, in your example, are 'Sam' and 'Tom'. They have no particular order which can safely be expected. It may be the natural order, or the order in which they are processed on the database (completely depending on the database implementation), or semi-random (such as iterating over items in a set). The order may also vary depending on whether the result was retreived from the data or from the cache. If you want a particular order, then specify it as order criterium: select distinct Name from Emp order by Name asc If you want the distinct values and the first record containing it, use group by: select min(ID), Name from Emp group by Name

Categories : SQL

SQL select from two tables, using a distinct value between the two IF it exists, otherwise only using value from the first table?
Try a LEFT JOIN instead. I assume you need to group by room as well: SELECT DISTINCT roomList.room, userlist.room, roomList.totalDesks, COUNT(DISTINCT userlist.desk) AS desksUsed, roomlist.totalDesks-COUNT(DISTINCT userlist.desk) AS desksOpen FROM roomlist LEFT JOIN userlist ON roomlist.room=userlist.room GROUP BY roomList.room, userlist.room, roomList.totalDesks

Categories : SQL

How to select distinct rows from table without using functions(min,max etc)?
You can select distinct rows using the row_number() function: select t.* from (select t.*, row_number() over (partition by <columns that should be different> order by NULL) as seqnum from t ) t where seqnum = 1;

Categories : SQL

Select fields from table with DISTINCT field
Use GROUP BY before the order clause and if you want order results first then group them then use subselect SELECT `user`, COALESCE(parents, 0) AS parentsEd, COALESCE(sons, 0) AS sonsEd FROM my_table GROUP BY `user` ORDER BY (parentsEd + sonsEd) DESC SELECT DISTINCT `user`, COALESCE(parents, 0) AS parentsEd, COALESCE(sons, 0) AS sonsEd FROM my_table ORDER BY (parentsEd + sonsEd) DESC SELECT a.* FROM ( SELECT DISTINCT `user`, COALESCE(parents, 0) AS parentsEd, COALESCE(sons, 0) AS sonsEd FROM my_table ORDER BY (parentsEd + sonsEd) DESC ) a GROUP BY a.`user`

Categories : Mysql

How to get distinct rows faster from a huge table?
You should talk to your DBA about your options, including indexing the column, and potentially more involved possibilities like a materialized view, if it's a frequently-executed query. One possibility to consider is to parallelise the query, but be aware that this could actually slow it down depending on where the bottleneck is. There's a white paper from Oracle on parallelisation. In principle you could add the parallel hint: select /*+ parallel */ distinct var1 from hr.hugetable You should discuss that with your DBA too, particularly the degree of parallelism (DOP) to use, and whether automatic DOP is appropriate. Also read up on what it does in the documentation, and compare the explain plans and timings - including at different DOP - to see what's appropriate. You don't want to r

Categories : SQL



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