Using DISTINCT On 2 Tables And Ref A Third.
Jan 5, 2006
Here's an example of what I am trying to do...
Table1: id, name, title,...
Table2: id, regionID, region,...
t1 has a 1 to many relationship to t2
(no keys on t2)
SELECT DISTINCT t1.name, t1.title
FROM table1 t1 INNER JOIN
table2 t2 ON t1.id = t2.id
That works fine... Now, I want to include a third table...
Table3: regionID , RegionName, location,...
SELECT DISTINCT t1.name, t1.title, t3.RegionName
FROM table1 t1 INNER JOIN
table2 t2 ON t1.id = t2.id INNER JOIN
table3 t3 ON t2.regionID = t3.regionID
A sample result would be....
Bill, Owner, England
Jim, Owner, France
Jim, Owner, Spain
However, I would like to only list the individuals (ie. Jim, Owner) one time! Only the first 2 fields (name, title) need to be distinct. How do I reference the third table without including it in the DISTINCT keyword?
Any guidance would be appreciated.
Thanks,
Andrew
View 5 Replies
ADVERTISEMENT
Jul 13, 2006
Hello !
for MS SQL 2000
SELECT tableA.idA, tableB.idB,tableB.ColX,tableA.ColA,
tableA.ColB, tableA.ColC
FROM tableB RIGHT OUTER JOIN tableA ON tableB.idB = tableA.idA
I want to do a DISTINCT on
tableB.ColX, tableA.ColA, tableA.ColB
to return only the rows tableB.ColX, tableA.ColA, tableA.ColB which are differents
thank you for helping
View 14 Replies
View Related
Oct 19, 2006
hello I am trying to get a distinct on one column and 2 tables but it doesnt work
Table1
ID_Table1
Name1
Number1
Table2
ID_Table2
ID_Table1
Name2
I want to get : ID_Table1, DISTINCT(Name1), Name2
WHERE Name1 LIKE 'A%'
how can I do it ?
thank you
View 4 Replies
View Related
Feb 28, 2014
// C - Categories, P - Parameters, CP_Link - linkage table between C and P
// Basically, it is a list of features belonging to a particular category.
DROP TABLE [C];
CREATE TABLE [C] (
ID_CAutoinc,
Name varchar(32),
PRIMARY KEY( ID_C )
[code]....
OK, this works fine, but I want to get a list of parameters (from P table) which holds only those items that linked to all categories requested. In other words, I want only 'leg' and 'hand', but not the 'beard', because 'girls' not linked to 'beard'.Only common features for all of categories listed "IN (,,,)" wanted.
View 3 Replies
View Related
Apr 4, 2007
hi friends
i have a query like this
query1="select co.*, ct.*, from contacts co, enquiries en, contactPersonType ct where co.co_contactId=en.en_contactId and co.co_contactType=ct.ct_contactTypeId"
i want to display only the contact persons details who made enquires.
this gives me duplicate records. im not displaying enquiry details. how can i use distinct with this?
thank you
neon
View 8 Replies
View Related
Jun 1, 2001
Attached is my select query, but it is returning the database values twice.
Can anyone tell me how I tell it to stop! A sub query I guess....
SELECT DISTINCT
dbo.Lead_Entry.Lead_ID, dbo.Lead_Entry.Lead_Source, dbo.Lead_Entry.Lead_Approved, dbo.Lead_Entry.Solution, dbo.Lead_Entry.Lead_Date,
dbo.Customer_Company.Company_Name, dbo.Customer_Contacts.Contact_Name
FROM dbo.Customer_Contacts INNER JOIN
dbo.Customer_Company ON dbo.Customer_Contacts.Company_ID = dbo.Customer_Company.Company_ID CROSS JOIN
dbo.Lead_Entry
View 4 Replies
View Related
Jun 4, 2014
I have 3 tables:
News:
bigint NewId
nvarchar NewTitle
datetime NewDate
nvarchar NewBrief
--------------------------
Category:
int CatId
nvarchar CatName
--------------------------
NewsRelCategory:
bigint Id
int CategoryIdFk
bigint NewsIdFk
--------------------------
I want to select NewId, NewDate and Distinct NewTitle
I tried this but NewTitle doesn't distinct:
SELECT
FROM dbo.Category INNER JOIN
NewsRelCategory ON dbo.Category.CatId = NewsRelCategory.NrcCategoryIdFk INNER JOIN
dbo.News ON NewsRelCategory.NrcNewsIdFk = dbo.News.NewId
View 9 Replies
View Related
Oct 29, 2014
I am attempting to run the following select statement joining multiple tables but in the end result I would like only Distinct/Unique values to be returned in the invlod.lodnum column.
[select pw.schbat, adrmst.adrnam, adrmst.adrln1, adrmst.adrcty, adrmst.adrstc, adrmst.adrpsz,
invlod.lodnum,
shipment.host_ext_id, shipment_line.ordnum, car_move.car_move_id
from aremst join locmst
on (aremst.arecod = locmst.arecod)
and (aremst.wh_id = locmst.wh_id)
[Code] .....
View 4 Replies
View Related
Nov 2, 2015
best way to go about this. My query works great and displays the information I require.
EG all demographic for ED, IP & OP. However if a patient has a record in each of the tables then I get the same info multiple times.
What I'm trying to do is a Select Distint against the entire query (below).
eg Select Distinct * from all below.
DECLARE @StartDate datetime = '01 oct 2015';
DECLARE @EndDate datetime = '30 OCT 2015';
--***OUTPATIENT DQ QUERY***--
SELECT distinct
HEYNo,
NHSNo2,
NHSNo1,
[code]....
View 3 Replies
View Related
Apr 27, 2008
Hello everybody,
have following problem:
I need info from 2 Tables. from the Table 2 I just need 1 column. When i ask for this column the output I get is data repeating themselve many times.
Distinct, should give me unique data, but is doesnt....
the code:
SELECT DISTINCT FSenddate, FSupplyIDName, FSupplyerNumber,FBillNo,FSourceBillNo,FItemName,FItemModel,
FAuxQty,FAuxTaxPrice,FHeadSelfP0237
FROM vwICBill_26
WHERE FSenddate BETWEEN DATEADD(dd,-14,GETDATE()) AND GETDATE()
This code just works in Table1 (vwICBill_26)
but with table 2 (vwICBill_1)
SELECT DISTINCT vwICBill_26.FSenddate,vwICBill_26.FSupplyIDName,
vwICBill_26.FSupplyerNumber,vwICBill_26.FBillNo,
vwICBill_26.FSourceBillNo,vwICBill_26.FItemName,
vwICBill_26.FItemModel,vwICBill_26.FAuxQty,
vwICBill_26.FAuxTaxPrice,vwICBill_26.FHeadSelfP0237,
vwICBill_1.FDate,vwICBill_1.FContractBillNo
FROM vwICBill_26,vwICBill_1
WHERE vwICBill_26.FSenddate BETWEEN DATEADD(dd,-14,GETDATE()) AND GETDATE()
AND vwICBill_1.FContractBillNo=vwICBill_26.FSourceBillNo
The last sentence is the problem
I want that it shows me the data that is not equal.
As soon as I implement the not equal it shows me the massive repeating data.
I mean even without the last sentence I get this data output.
All together, I want a clear database output without data repeating.
Any ideas how it may work without DISTINCT?
I think this problem is a typical amateure problem, but I would apreciate help!
View 2 Replies
View Related
Aug 19, 2015
I need to get all distinct values from 10 different tables that exist in the field [favoritesport] And each table holds close to 50K records so I am looking at 500,000 records to get distinct values for. Would the fastest, less intrusive way of achieving this be to just create a UNION ALL so run
Select Distinct([favoritesport]) from table1
Union
Select Distinct([favoritesport]) from table2
Union
Select Distinct([favoritesport]) from table3
etc etc etc
View 2 Replies
View Related
Jul 20, 2005
12.) Now you have two different tables - each with two columns.Table #1Single Column2 rows with a value equal to 1 and 2Table #2Single column2 rows with a value equal to 2 and 4Construct a statement returning in a single column all the valuescontained, but not the common values.This is another question that I got in an interview, I missedit.......Thanks,Tim
View 1 Replies
View Related
Jan 15, 2015
I am trying to create a query that outputs the following data from multiple tables. Here is an example of the raw data right now
Date | MachineNumber | TestName
---------------------------------------
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 510 | NewTest
1/1/2015 | 510 | NewTest
1/1/2015 | 510 | NewTest
1/1/2015 | 620 | Test#1
Here is the desired counted output, I would like to pull distinct Date, MachineNumber, TestName and then count how many times they occur in the raw data form.I do need to perform a case on the date because right now its in a datetime format and I only need the date.
Date | MachineNumber | TestName | TestOccuranceCount
-----------------------------------------------------------------
1/1/2015 | 500 | Something | 4
1/1/2015 | 510 | NewTest | 3
1/1/2015 | 620 | Test#555 | 1
I am pulling three columns with the same names from 8 different tables. What I need to display the date, machine & test name and count how many times a test was run on a machine for that date. I have a feeling this can be handled by SSAS but haven't built an analysis cube yet because I am unfamiliar with how they work. I was wondering if this is possible in a simple query. I tried to set something up in a #Temp table. Problem is the query takes forever to run because I am dealing with 1.7 Million rows. Doing an insert into #temp select columnA, columnB, columnC from 8 different tables takes a bit.
View 9 Replies
View Related
Jul 6, 2007
Hi, I have the following script segment which is failing:
CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude))
INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache
When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'."
Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values.
The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why.
Any ideas?
View 2 Replies
View Related
Mar 12, 2007
I need to run a SELECT DISTINCT query acrossmultiple fields, but I need to add another field that is NON-DISTINCTto my record set.Here is my query:SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, genderFROM gpresultsWHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis query runs perfect. No problems whatsoever. However, I need toalso include another field called "admitdate" that should be treatedas NON-DISTINCT. How do I add this in to the query?I've tried this but doesn't work:SELECT admitdateFROM (SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, gender from gpresults)WHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis has to be simple but I do not know the syntax to accomplishthis.Thanks
View 2 Replies
View Related
May 8, 2006
Hello,
I have written a small asp.net application, which keeps record of the proposals coming from the branch offices of a bank in a tableCREATEd as a TABLE Proposals ( ID smallint identity(7,1), BranchID char(5), Proposal_Date datetime )
This app also calculates the total number of proposals coming from a specific branch in a given date bySELECTing COUNT(BranchID) FROM Proposals WHERE BranchID=@prmBranchID AND Proposal_Date=@prmDateand prints them in a table (my target table).
This target table has as many rows as the result of the "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals"and excluding the first column which displays those DISTINCT Proposal_Dates, it also has as many columns as the result of the"SELECT DISTINCT BranchID FROM Proposals". This target table converts the DateTime values ToShortDateString so that we are able to see comfortably which branch office has sent how many proposals in a given day.
So far so good, and everything works fine except one thing:
Certain DateTime values in the Proposals table which are of the same day but of different hours (for ex: 11.11.2005 08:30:45 and11.11.2005 10:45:30) cause some trouble in the target table, where "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals" is executed, because (as you might already guess) it displays two identical dates in ShortDateString form, and this doesn't make much sense (i.e. it causes redundant rows)
What I need to do is to get a result like (in a neat fashion :)
"SELECT COUNT( DISTINCT Proposal_Date ) <<DISTINCT ONLY IN THE DAYS AND NOT IN HOURS OR MINUTES OR SECONDS>> FROM Proposals"
So, how to do it in a suitable way?
Thanks in advance.
View 4 Replies
View Related
Mar 29, 2007
suppose i have aDataset with 11 rows. field1 with 5 rows of aaa, 6 rows of "bbb"
I want's some thing like
field1 rowcount
aaa 5
bbb 6
View 1 Replies
View Related
Jan 9, 2015
Okay, I've been working on this for a couple of hours with no success. I'm trying to find the number of telephone numbers that are associated with multiple students at different school sites. I've created a temp table that lists all phone numbers that are associated with more than one student. I'm now trying to query that table and count the number of telephone numbers that are associated with more than one site. Essentially, I'm looking for parent/guardians that have students at different sites.
Here's an example of what I'm hoping to accomplish:
*In this example, I'm just trying to get a count of the different/distinct school sites associated with each number. If I can, at the same time, limit it to a count of > 1 (essentially excluding parents with students at the same site), even better :)
===================================
Temp table
===================================
SCHOOL | STU_ID | STU_PHONE
101 | 12345 | 111-222-3333
101 | 23456 | 111-222-3333
102 | 34567 | 111-222-3333
101 | 45678 | 999-888-7777
101 | 56789 | 999-888-7777
101 | 67890 | 555-555-5555
102 | 78901 | 555-555-5555
103 | 89012 | 555-555-5555
==================================
Wanted query results
==================================
STU_PHONE | #Students | UNIQUE_SCHOOLS
111-222-3333 | 3 | 2
999-888-7777 | 2 | 1
555-555-5555 | 3 | 3
View 1 Replies
View Related
Jan 24, 2008
I'm new to my company, although not new to SQL 2005 and I found something interesting. I don't have an ERD yet, and so I was asking a co-worker what table some data was in, they told me a table that is NOT in SQL Server 2005's list of tables, views or synonyms.
I thought that was strange, and so I searched over and over again and still I couldn't find it. Then I did a select statement the table that Access thinks exists and SQL Server does not show and to my shock, the select statement pulled in data!
So how did this happen? How can I find the object in SSMS folder listing of tables/views or whatever and what am I overlooking?
Thanks,
Keith
View 4 Replies
View Related
Sep 27, 2007
If I know this SELECT will get me unique username to configname records:Select DISTINCT configname, username FROM EtechModelRequests
JOIN CC_host.dbo.usr_smc ON
username = user_id
JOIN Webservices.dbo.PartNumberPricingImport ON
PartNumber = configname
Where RequestDateTime > '9/26/2007' And country = 'US' And interfacename Like '%download%' And result = 0
How do I show the other fields I need? The field I need is List Price but I don't want to DISTINCT on it too.
View 2 Replies
View Related
Mar 31, 2008
Hi I have two tables linked by MemberID and I'll like to produce a list from the two tables but also want to specify one table to only use the Top 50 distinct records
Table 1 (top 50 Distinct)
Memberid (distinct)PictureDateTakenGalleryID
Order By DateTaken
Table 2
MemberidFirstNameLastNamePrivatelist
MemberidPictureFirstNameLastNameGalleryIDPrivate
View 6 Replies
View Related
Apr 2, 2004
I have a query and it is bringing up multiples of the same data
what i would like to know is if there is a way to use something like Distinct that i can use as a clause
such as
Select *
from Table
Where Distinct ColumnName
I know that distinct doesnt work in this situation
I would like to know if there is a command to do this or a way to fix it
if i use this
select distinct columnName,columnName2
from table
it returns the rows where columnName and ColumnName2 both are not the same
My purpose is that i need to select more than one column but i would like non duplicates based on ONE column name
thanks for any help
View 20 Replies
View Related
May 31, 2002
I have 6 fields in my table and I want to display only distinct values from one of those columns. However, I also need to use the fields from the remaining 5 columns in my asp page. For example,
Columns:
CompID
CompanyName
Ticker
Industry
CEOName
MarketCap
In one table on the page, I want to display only unique entries for 'CompanyName' which I've been doing with this statement:
SqlText = "SELECT DISTINCT CompanyName FROM [tablename] WHERE UserID=" & intUserID & ""
However, I also need to use the other values associated 'CompanyName' in my asp page after opening my recordset. I need to display <%=Rs("CompID"%>, <%=Rs("Ticker"%>, <%=Rs("Industry"%> etc. while maintaining the DISTINCT portion of my statement.
Thanks again.
View 1 Replies
View Related
Sep 12, 2006
I have 3 records in a db..
Table
id
file_name
category
Records
1
bob.jpg
male
2
rob.jpg
male
3
pam.jpg
female
I am trying to grab the distinct category so only 'male and female' is output, but i am also needing to grab the id, file_name to use later on the page as well.
When i try
select distinct category, id, file_name
from tbl_pictures
it outputs all the records since there is no exact match in all 3 fields,
but i am wanting this to happen
select distinct category
from tbl_pictures
but i am still needing to grab the other two fields because i need to use them in the next part of the page
how can i go about doing this
View 1 Replies
View Related
Apr 22, 2008
What is the difference between distinct and group by please?
View 4 Replies
View Related
Apr 24, 2008
Hi,
I have data in several tables and Im having trouble filtering the data.
This is statement that Im executing:
1. SELECT DISTINCT Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName, PersonSkills.Skills
This is the statement that gives me the results I want:
2. SELECT DISTINCT Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName
The problem with this is I need to have Persons.Skills in the statement and DISTINCT doesnt filter the data the way I need it to because the data in Persons.Skills is different which results in I get duplicated results.
Is it possible to do something like
SELECT DISTINCT column1,..,column5,(SELECT Persons.Skills)
by this I mean to apply distinct to some of the columns in the SELECT statement?
View 6 Replies
View Related
May 20, 2008
I require only the distinct objects that were depend on my stored procedures
i tried for sp_depends test_sp
for this
dbo.iim_CPL_fsl_tree_tbl_tmpuser tablenonolayout_slno
dbo.iim_CPL_fsl_tree_tbl_tmpuser tablenonolayout_type
dbo.iim_CPL_fsl_tree_tbl_tmpuser tablenonolevel1
dbo.iim_CPL_fsl_tree_tbl_tmpuser tablenonolayout_operator
dbo.iim_CPL_fsl_tree_tbl_tmpuser tablenonorlevel1desc
dbo.iim_CPL_fsl_tree_tbl_tmpuser tablenonorlevel2desc
dbo.iim_CPL_fsl_tree_tbl_tmpuser tablenoyesguid
dbo.abb_account_budget_dtluser tablenonocompany_code
dbo.abb_account_budget_dtluser tablenonofb_id
dbo.abb_account_budget_dtluser tablenonofin_year_code
dbo.abb_account_budget_dtluser tablenonofin_period_code
dbo.abb_account_budget_dtluser tablenonoaccount_code
being dbo.abb_account_budget_dtl were repeated more than once.i need only the distinct objects..any other system procedure is there??
View 1 Replies
View Related
Jun 3, 2008
I'm trying to find the Distinct for CODEID but it's still returning duplicate data. What is the best way to write this query so that the result I get are Distinct Codeid?
SELECT DISTINCT dbo.AgreementDurationCode.CodeID, dbo.Item.PartNumber, dbo.ItemShadow.AgreementDurationCode, dbo.AgreementDurationCode.CodeCategory,
dbo.AgreementDurationCode.CodeCategoryID, dbo.AgreementDurationCode.Code, dbo.AgreementDurationCode.CodeName,
dbo.AgreementDurationCode.CodeAbbreviation, dbo.AgreementDurationCode.CodeShortAbbreviation, dbo.Item.ItemName,
dbo.AgreementDurationCode.CodeMaxcimAbbreviation, dbo.ItemShadow.ItemStatusCode, dbo.ItemShadow.LicenseTypeCode
FROM dbo.Item INNER JOIN
dbo.ItemShadow ON dbo.Item.ItemID = dbo.ItemShadow.ItemID INNER JOIN
dbo.AgreementDurationCode ON dbo.Item.AgreementDurationCodeID = dbo.AgreementDurationCode.CodeID
WHERE (dbo.ItemShadow.LicenseTypeCode IN ('SEL', 'OLP', 'OLV')) AND (dbo.ItemShadow.ItemStatusCode = 'COM')
View 3 Replies
View Related
Apr 18, 2014
I have data like this
course_id Session
--------- --------
1234 8.00 - 10.00
4567 8.00 - 10.00
From the above table i want the result to be like this
course_id1 course_id2 session
---------- ---------- -------
1234 4567 8.00 - 10.00
Looking for query to get the above result format.
View 3 Replies
View Related
Mar 13, 2007
Hi All,
I am using SQL Server 2000. I have 3 fields in my table. I want to do distinct on one field but also want to show the remaining two fields in the result. How can I do that?
Thanks
-G
View 6 Replies
View Related
Apr 6, 2007
Is there some way to use the distinct keyword so that it applies ONLY to a subset of the items in the select list???
For example, suppose I want to
select col_1, col_2, col_3, col_4
but I do not want distinct applied against all 4 items...
Maybe I want all 4 items in the selection list,
but I want distinct to use only col_3 as its filtration criteria...
I know the syntax shown below is not valid, but I am showing it anyway because I am hoping it will illustrate what I am looking for...
Is there a VALID syntax that is something like this???
select col_1, col_2, (distinct col_3), col_4
or
select col_1, col_2, distinct (col_3), col_4
View 5 Replies
View Related
May 22, 2007
I am trying to pull information out of a table using distinct, but instead of just pulling a certain column I want to pull multiple columns in a row?
However when I use the command below I only get the "workitem_number" column available, where there are approx. 4 other columns that I need (workitem_title, workitem_comment, ETC) When I add the additional columns (after the distinct statement) it doesn't work due to the type of data.
"SELECT DISTINCT workitem_number from workitem_cost_view where assigned_to_worker_nt_id = '" & Request.QueryString("Name") & "' AND workitem_start_on = '" & Request.QueryString("schDate") & "' order by workitem_number"
Is there any way to pull multiple columns, based only on distinct for one column?
I hope I'm explaining this correctly.
Thanks.
Rwj6001
View 13 Replies
View Related
Sep 1, 2007
Hi
I have table 'TblProd' and columns ID, categories, make, description, model. I am using the below procedure
create procedure getsubcategories
@category nvarchar (32)
As
Select ID, make, model from TblProd where category = @category
GO
My question how do I get distinct 'make' and other respective columns.
advance thanks
View 11 Replies
View Related