w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Entity Framework Distinct records from multiple tables
Well this should work without many changes, though i would have wrote it entirely in expressions: var distinctValues = (from a in dataContext.A_Table join b in dataContext.B_Table on a.EmpID equals b.EmpID join c in dataContext.C_Table on b.SomeID equals c.ID where a.IsActive == true && a.ID == id select new NewClass() { ID = c.ID, Name = c.Name }).ToList() .GroupBy(x=>new {ID = x.ID,Name = x.Name}) .Select(x=>new {ID = x.Key.ID,Name = x.Key.Name});

Categories : C#

Compare 2 columns in 2 tables with DISTINCT value
It looks like the USER_ID value is the concatenation of your ORG_ID and something to make it unique. I'm assuming this is from a COTS product and nothing a human would have built. Your desire is to find out how many entries there are by department. In SQL, when you read the word by in a requirement, that implies grouping. The action you want to take is to get a count and the reserved word for that is COUNT. Unless you need something out of the TBL_ORG, I see no need to join to it SELECT LEFT(T.USER_ID, 3) AS USER_CREATED , COUNT(1) AS GroupCount FROM TBL_USER AS T GROUP BY LEFT(T.USER_ID, 3) Anything that isn't in an aggregate (COUNT, SUM, AVG, etc) must be in your GROUP BY. SQLFiddle I updated the fiddle to also show how you could link to TBL_ORG if you need an elemen

Categories : SQL

Need logic for joining two tables and using distinct
The below solution meets your needs select T2.[index],Avg(T1.[Current]) as [Current] ,avg(t1.[bpo]) as bpo,avg(t1.[Forecast]) as [Forecast] from Table1 T1 join Table2 T2 on isnull(T1.[type],'') = t2.[type] and isnull(t1.code,'') = t2.code group by T2.[index]

Categories : SQL

Select distinct values from tables in sql
Use distinct in your count() SELECT * FROM `student_discipline` a LEFT JOIN `discipline` b ON a.disc_id = b.id LEFT JOIN `student` c ON a.stud_id = c.id WHERE a.mark > 4 GROUP BY c.id HAVING COUNT(distinct b.id) >= 5

Categories : Mysql

Select distinct totals from access tables
I assume the three tables each have two columns: Run_Date and Total Defects, Not Closed or Closed. If you have a table with all the run dates in it, something like this will work: SELECT RunDates.Run_Date, T_D.[Total Defects], T_O.[Not Closed], T_C.[Closed] FROM (( RunDates LEFT JOIN T_C ON RunDates.Run_Date = T_C.Run_Date) LEFT JOIN T_O ON RunDates.Run_Date = T_O.Run_Date) LEFT JOIN T_D ON RunDates.Run_Date = T_D.Run_Date If not, you will need to construct one using a UNION (not UNION ALL): SELECT RunDates.Run_Date, T_D.[Total Defects], T_O.[Not Closed], T_C.[Closed] FROM (( (SELECT Run_Date FROM T_C UNION SELECT Run_Date FROM T_O UNION SELECT Run_Date FROM T_D) AS RunDates LEFT JOIN T_C ON RunDates.Run_Date = T_C.Run_Date) LEFT JOIN T_O ON R

Categories : Ms Access

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

MySQL - Getting count of distinct values from 3 tables
Just UNION the tables together and COUNT. The UNION will remove duplicates, so a simple COUNT will do the trick - a COUNT DISTINCT won't be needed. This will work if you want the count only; if you want other values the query gets slightly more involved: SELECT COUNT(*) FROM ( SELECT employee_no FROM complete_shifts WHERE date = '2013-06-13' UNION SELECT employee_no FROM incomplete_shifts WHERE date = '2013-06-13' UNION SELECT employee_no FROM incomplete_shift_register WHERE date = '2013-06-13' ) AllShifts

Categories : Mysql

SQL Server DISTINCT OR GROUP BY WITH TEMP TABLES
Try using the first query you listed as a subquery, and join it to tblapsdata. It will look something like this: DECLARE @CustomerID int = 8, @UtilityCompanyID int = 1 SELECT MD.SERV_ACCT, AD.ACCOUNT, MD.MAXINVDATE FROM (SELECT DISTINCT SERV_ACCT, MAX(INV_DATE) as MAXINVDATE FROM tblAPSData WHERE SERV_ACCT NOT IN (SELECT ServiceAccount FROM tblMEP_Meters JOIN tblMEP_Sites ON tblMEP_Meters.SiteID = tblMEP_Sites.ID JOIN tblMEP_Projects ON tblMEP_Projects.ID = tblMEP_Sites.ProjectID WHERE CustomerID = 8 AND Type = 1 ) AND ACCOUNT IN (SELECT AccountNumber FROM tblMEP_CustomerAccounts WHERE CustomerID = @CustomerID AND UtilityCompanyID = @Util

Categories : SQL

Distinct doesn't work when joining two tables
Correct. distinct returns distinct of all the fields. If you want distinct EmployeeIds, then try something like: SELECT distinct e.EmployeeID FROM Employee AS e INNER JOIN DealerSessions AS ds ON e.EmployeeID = ds.EmployeeID WHERE (ds.SessionID = 154) The additional fields are resulting in multiple rows. If you want one row, then you can do something like this: select EmployeeID, Name, Surname, DealerSessionsID, SessionID, Expr1 from (SELECT e.EmployeeID, e.Name, e.Surname, ds.DealerSessionsID, ds.SessionID, ds.EmployeeID AS Expr1, row_number() over (partition by e.EmployeeId order by (select NULL)) as seqnum FROM Employee AS e INNER JOIN DealerSessions AS ds ON e.EmployeeID = ds.EmployeeID WHERE (ds.SessionID = 154) ) t where seqnum =

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

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

get the average from two distinct columns joining other tables in MySQL
This is your answer. but you should conside now month is aug not march b/c i used now(). okey. so change your table data accordingly and check the result. select AVG( loc) as loc,AVG(qty) as qty from tbl1 where month NOT IN (concat(UCASE(DATE_FORMAT(date_add(now(), INTERVAL -1 MONTH),'%b')),',', UCASE(DATE_FORMAT(date_add(now(), INTERVAL -2 MONTH),'%b')),',', UCASE(DATE_FORMAT(date_add(now(), INTERVAL -3 MONTH),'%b')))) group by type For DEMO Click here:-http://sqlfiddle.com/#!2/2a15e/15

Categories : Mysql

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

Nesting a Select Distinct and returning all results from Joined tables
use Plantation select distinct tblmember_v62.Acct, tblmember_v62.First, tblmember_v62.Last, tblMemberAccount_v62.Balance from tblmember_v62, tblMemberAccount_v62 Where dbo.tblMember_v62.MemberId = tblmemberaccount_v62.MemberId and tblMemberAccount_v62.Balance > '0.00' and tblMember_v62.BillingNotify = '2' and (tblMemberAccount_v62.InsertDate > '2012-01-01 00:00:00.000' or tblMemberAccount_v62.UpdateDate > '2012-01-01 00:00:00.000') Order by dbo.tblMemberAccount_v62.MemberId, tblmember_v62.Last

Categories : SQL

SQL and storing foreign keys on multiple tables vs joining through tables for queries
How much data do you expect to store in your database? If there are relatively small amount of rows in each table for the joins the execution time between a normalized and non-normalized database will be negligible. So instead just go with a design that makes sense to you and you can normalize and de-normalize tables where it makes sense. Here's a good read on the subject:http://www.codinghorror.com/blog/2008/07/maybe-normalizing-isnt-normal.html

Categories : SQL

SQL multiple Select ... where id in and a DISTINCT
You should be using joins and consider creating indexes at least on foreign keys Try the following SELECT DISTINCT TP_Test_Info.id, TP_Test_Info.name FROM TP_Test_Info JOIN TP_Test_Sections ON TP_Test_Sections.test_id = TP_Test_Info.id JOIN TP_Test_Questions ON TP_Test_Questions.section_id = TP_Test_Sections.id JOIN TP_Student_Answers ON TP_Student_Answers.question_id = TP_Test_Questions.id WHERE TP_Student_Answers.student_id = 751

Categories : Mysql

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

Distinct count with multiple columns
It sounds like you want to count only the records with minimum Relevancy for each ID. Therefore, you can do (SQL Fiddle example): SELECT Category, COUNT(1) FROM Table1 t1 WHERE NOT EXISTS ( SELECT 1 FROM Table1 t2 WHERE t2.ID = t1.ID AND t2.Relevancy < t1.Relevancy ) GROUP BY Category

Categories : Mysql

Query with multiple distinct in a column
You should just use conditional summation: select count(*), sum(case when text = '5' then 1 else 0 end) as Num_5, sum(case when text = '10' then 1 else 0 end) as Num_10, sum(case when text is null then 1 else 0 end) as Num_Null from t_test; This is assuming that a field called text is stored as a character string, so the constants are put in quotes. If it is really a number, first I'd be curious why it is called text. In that case, you can dispense with the single quotes. In your case, the last one doesn't work because count(text) counts non-null values. But the where clause only keeps NULL values. For that one, you should use count(*). The correct query would be: select count(*) from t_test where text is null;

Categories : Dotnet

Best way to display only distinct value from multiple div tags with same class name
Probably somthing like this? $('.categories').filter(function(){ //use filter on all .categories var txt = $.trim(this.innerHTML); //Get the text of current return ($(this).nextAll().filter(function () { //filter all of the proceeding siblings which has the same text return $.trim(this.innerHTML) === txt }).length); //send true or false (in fact truthy or falsy to ask to hide the current element in question) }).hide(); Fiddle Another derivative and this will hide the first ones while the prev ones will hide the last one. $(function () { $('.categories').each(function(){ var txt = $.trim(this.innerHTML); $(this).nextAll(':visible').filter(function () { return $.trim(this.innerHTML) === txt }).hide(); });

Categories : Javascript

Using distinct in Spring data over multiple columns
@Query return ArrayList of Object(s) instead of specific type of object. so you have to define some thing like @Query("select distinct a.name, a.xAxis, a.yAxis from CollectedData a") List<Object> findAllDistinctData(); then cast according to your requirement, List<Object> cdataList=findAllDistinctData(); for (Object cdata:cdataList) { Object[] obj= (Object[]) cdata; String name = (String)obj[0]; String description = (String)obj[1];; ... }

Categories : Spring

How to insert multiple values into a Row if 1 field is distinct
insert into Locals (Street, PC, Locality) select b.Street, b.PC, b.Locality from Locals_bk as b where not exists (select * from Locals as t where t.street = b.street) or insert into Locals (Street, PC, Locality) select b.Street, b.PC, b.Locality from Locals_bk as b where b.street not in (select t.street from Locals as t)

Categories : SQL

How To Select Distinct Row Based On Multiple Fields
WITH CTE AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY Owner ORDER BY Date DESC) AS RN FROM tablename ) SELECT ID, Name, Date, Location, Owner FROM CTE WHERE RN = 1;

Categories : SQL

Getting Count Only of Distinct Value Combinations of multiple fields.
The subquery/CTE method is the "right" way to do it. A quick (in terms of typing but not necessarily performance) and dirty way is: select count(distinct customername+'###'+Planet) from #Customer; The '###' is to separate the values so you don't get accidental collisions.

Categories : SQL

mysql COUNT DISTINCT multiple columnes with WHERE
Are you looking for the count per pid? If so, this is a simpler query: SELECT pid, SUM(location like '%a%') as As, SUM(location like '%b%') as Bs, SUM(location like '%c%') as Cs FROM db.t WHERE (date > @dt) GROUP BY pid

Categories : Mysql

LINQ Distinct on multiple properties both directions
Create an extension method such as this one: public static IEnumerable<TSource> DistinctBy<TSource, TKey>(this IEnumerable<TSource> source, Func<TSource, TKey> keySelector) { var seenKeys = new HashSet<TKey>(); return source.Where(element => seenKeys.Add(keySelector(element))); } Then use it. See how that works for you.

Categories : C#

Count distinct value pairs in multiple columns in SQL
Get all distinct id, name and address columns and count the resulting rows. SELECT COUNT(*) FROM mytable GROUP BY id, name, address

Categories : SQL

LINQ to SQL - Distinct records across multiple columns
Group results by ProductCode (1) and select from each group item with max Version value (2): results = (from a in context.ALLProducts let prodCode = a.ProductCode let prodDesc = a.ProductDescription where legList.Contains(a.LegislationID) && (a.LanguageID == pLanguage || pLanguage == null) && a.BrandName == pMarket && (prodCode.Contains(pSearch) || prodDesc.Contains(pSearch) || pSearch == string.Empty) && prodCode[0] != 'x' && ((pMarket == "testMarket") ? prodCode.StartsWith("0") : true) group a by a.ProductCode into g // 1 let lastProduct = g.OrderByDescending(x => x.V

Categories : C#

MySQL distinct results on duplicate multiple columns
The following gets the list of env/prop where you have a problem: select env, prop from t group by env, prop having count(distinct value) > 1; This joins back to the original table to get the records with the problem: select t.* from t join (select env, prop from t group by env, prop having count(distinct value) > 1 ) tep on t.env = tep.env and t.prop = tep.prop;

Categories : Mysql

Django Filter distinct field from multiple table
You can split input string first: key_skill, title = "python,web developer".split(',') Then filter jobs cross models: jobs = Job.objects.filter(title=title, employerkeyskills__keyskills=key_skill)

Categories : Python

c# linq select distinct of elements with multiple attribute
I want to return distinct tool and process attribute. It sounds like you want this this: var results = from e in apcxmlstate.Elements("thread") group e by Tuple.Create(e.Attribute("process").Value, e.Attribute("tool").Value) into g select g.First().Attribute("tool").Value; Or in fluent syntax: var results = apcxmlstate .Elements("thread") .GroupBy(e => Tuple.Create(e.Attribute("process").Value, e.Attribute("tool").Value)) .Select(g => g.First().Attribute("tool")); This will return the tool for each distinct tool / process pair—given your example set { "atool", "btool", "atool", "ctool" }. However, if all you want is distinct tool values you can just do this: var results = apcxmlst

Categories : C#

Put unique distinct values into a list from multiple columns of an array
One way to do this is create a function that returns the array of your unique cells and then multiplies them all by matches in your Marks column. Create the unique cells with this array function. Note this function uses the Dictionary object. In the VB Editor, go to Tools > References, and make sure Microsoft Scripting Runtime is selected. Public Function UniqueValues(aRange As Range) Dim DictValues As New Dictionary Dim cll As Variant Dim aryResults() As String For Each cll In aRange If Not DictValues.Exists(cll.Value) Then DictValues.Add cll.Value, "": Next UniqueValues = DictValues.Keys Set DictValues = Nothing End Function Enter in cell H3 and press CTRL SHIFT RETURN (as it's an array function) =TRANSPOSE(uniquevalues(B3:E6)) and drag down to H15 or beyond We have to use

Categories : Excel

Extracting Multiple Similar Rows For Each Distinct Type Of A Column
Basically, you can get the record that is present in all fruits regardless of name via this query, SELECT col2, col3 FROM tableName GROUP BY col2, col3 HAVING COUNT(*) = (SELECT COUNT(DISTINCT col1) FROM tableName) SQLFiddle Demo The total number of record in a group of two columns: col2 and col3 must be equal to the total number of fruits. SELECT COUNT(DISTINCT col1) FROM tableName. So to get all the records that has a combination present in all fruits, we need to JOIN it with the table itself. SELECT a.* FROM tableName a INNER JOIN ( SELECT col2, col3 FROM tableName GROUP BY col2, col3 HAVING COUNT(*) = (SELECT COUNT(DISTINCT col1) FROM tableName) ) b ON a.col2 = b.col2 AND a

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

Export Access database with multiple tables to Excel with multiple sheets
Your db variable doesn't refer to the current database (yet - it is set in the following line), and the property is Name (I haven't encountered DatabaseName): Sub Test() Dim db As DAO.Database Set db = CurrentDb Dim sLast As String MsgBox db.Name 'F:Documents and SettingsstudentMy DocumentsStaff Database.accdb sLast = InStrRev(db.Name, "") MsgBox Right(db.Name, Len(db.Name) - sLast) 'Staff Database.accdb End Sub Name gives the full path and filename, the second MsgBox reduces this to just the filename. (There may be another way to get the filename without having to parse Name..) To get the number of rows exported you could open a Recordset for the table(s), reading the RecordCount property. A text file could be created using the FileSystemObject object.

Categories : Excel

Database Design: Multiple User Entities. Single or Multiple Tables
Option 1 looks better to me. It will simplify your code when you don't care to distinguish students from coaches, and will be pretty much the same as option 2 if you want to distinguish them. If you really need to validate the foreign keys you can use triggers to check if its a coach or not. I'm not sure what you mean by "Potential polymorphism issues and the need to normalise down the track.".

Categories : PHP

mysql inner join same table and same column multiple times from multiple tables
Yes, the better way to do this for maintenance purposes is to create a view of only the inner joins and querying the view. Remember in terms of performance there would be little by way of improvement but maintenance of the code would become much easier. Given your purpose the inner joins are not entirely avoidable unless you decide to change the structure of the tables

Categories : Mysql

query MySQL data from multiple tables with multiple entries with PHP
You can do this by matching to a subquery that gets the latest comment_added for each injury_id. SELECT b.injury_id, bl.injury_id b.name, b.body_part, b.first_mention, b.last_changed, b.status FROM injury b INNER JOIN injury_list bl ON b.injury_id = bl.injury_id WHERE (bl.injury_id, bl.comment_added) IN ( SELECT injury_id, MAX(comment_added) FROM injury_list GROUP BY injury_id) ORDER BY status, last_changed DESC

Categories : PHP

MSQL select regardless of distinct or not distinct (count greater than zero)
In the data in the question, there is no repeating id in table A. The query should be returning only one row from A, then. However, your query has an error. The second join should be back to the first table, not the second: SELECT a.col1, b.col1, c.col2 FROM a LEFT JOIN b ON b.id = a.id LEFT JOIN c ON c.id = a.id WHERE a.id = 1 You had c.id = b.id and b.id could be NULL if there is no match in that table. Also, from your description, I wonder if a union would be the best approach: select a.col1, 'a' from a where id = 1 union all select b.col1, 'b' from b where id = 1 union all select c.col2, 'c' from c where id = 1

Categories : Mysql



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