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

Return 7 Col But DISTINCT On 3 Col From 2 Tables

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

One Column DISTINCT On 2 Tables

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

How To Get Distinct List From Two Tables

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

Distinct With Multiple Tables

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

How To Select Distinct Records From 3 Or More Tables...?

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

T-SQL (SS2K8) :: Selecting Distinct From Multiple Tables

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

Select Distinct Column While Joining Tables

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

Transact SQL :: Distinct Values From Multiple Tables?

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

2 Tables = Redundant Data/ DISTINCT Doesnt Work

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

Transact SQL :: How To Get All Distinct Values From 10 Different Tables That Exist In The Field

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

How To Bring Back The Distinct Values In Single Column From Two Tables

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

SQL Server 2012 :: Row Counts Based On Distinct Values From Multiple Tables

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

Select DISTINCT On Multiple Columns Is Not Returning Distinct Rows?

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

Trying To Add A NON-DISTINCT Field To A DISTINCT Record Set In A Query.

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

DISTINCT To ShortDateString, Not DISTINCT To The DateTime; How?

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

How To Show Distinct Rows Of The Column Of The Dataset And Number Of Distinct Rows Of That Column

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

Distinct Values Among Distinct Column Values

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

Why Would Tables Pulled In From ODBC In Access Be Different Than Tables In SQL 2005 Tables?

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

Not Too Many Distinct..

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

Distinct And Top

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

Distinct

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

Using DISTINCT

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

Using Distinct

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

Distinct

Apr 22, 2008

What is the difference between distinct and group by please?

View 4 Replies View Related

DISTINCT... Help :)

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

Distinct Value

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

DISTINCT

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

How To Get Distinct ID With Same Value

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

DISTINCT

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

Using DISTINCT

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

Using Distinct

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

How To Have Distinct Value?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved