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

Select Distinct Records

Oct 10, 2007

Hello, I have the following tables: declare @B table (Bid int identity, description varchar(50)) declare @P table (Pid int identity, Bid int, description varchar(50)) declare @T table (Tid int identity, description varchar(50)) declare @TinP table (TinPid int identity, Tid int, Pid int) insert into @B (description) select 'B1' insert into @B (description) select 'B2' insert into @P (description, Bid) select 'P1', 1 insert into @P (description, Bid) select 'P2', 1 insert into @P (description, Bid) select 'P3', 2 insert into @T (description) select 'T1' insert into @T (description) select 'T2' insert into @T (description) select 'T3' insert into @TinP (Tid, Pid) select 1, 2 insert into @TinP (Tid, Pid) select 2, 2 insert into @TinP (Tid, Pid) select 3, 3 select * from @B select * from @P select * from @T select * from @TinP I need to get all records in T (Tid and description) which are related to a given BId So for @Bi = 1 I would get: Tid Description 1 T1 2 T2 So I need the distinct values. How to solve this? Thanks, Miguel

View 1 Replies View Related

Select Distinct Records With Multiple Joins

Mar 13, 2008

View 3 Replies View Related

How To Use Distinct To Select Unique Records But Without Sorting ?

May 30, 2008

Hi

I need to select unique records from a Table. I'm using Distinct Keyword for this purpose. But the result set is showing distinct records in sorted order. I do'nt want to sort records. I need the order in which they are created in table.

Please suggest me the solution for this problem.

Thanks

Regards

Avinash Vyas

View 4 Replies View Related

Transact SQL :: Exclude A Distinct Records From Select When One Condition Is True?

May 28, 2015

I have customers named Alex (Cid=1), Bob (Cid=2), and Carrie (Cid=3) in a table customer.

Cid
First_Name
1
Alex
2
Bob
3
Carrie

I have products name Gin (Pid=1), Scotch (Pid=2) and Vodka (Pid=3) in a table products.

Pid
Product_Name
1
Gin
2
Scotch
3
Vodka

And I have a table that holds purchase called Customer_Purchases that contain the following records:

Cid
Pid
1
1
1
2
2
1
2
3
3
2

I would like to make a marketing list for all customers that purchased Gin or Scotch but exclude customers that purchased Vodka. The result I am looking for would return only 2 records: Cid’s 1 (Alex) and 3 (Carrie) but not 2 (because Bob bought Vodka).

I know how to make a SELECT DISTINCT statement but as soon as I include Pid=2 This clearly doesn’t work :

SELECT DISTINCT Pid, Cid
FROM
Customer_Purchases
WHERE (Cid = 1) OR
(Cid = 3) OR
(Cid <> 2)

View 3 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

SQL Select Records NOT In Both Tables

Aug 7, 2005

HiI'm using Access 2002. I have 2 tables tblGroupContact,tblGroupPermission, both have 2 fields identical structure:ContactID GroupID (Both are Composite keys and both hold integers)tblGroupContact holds everybody and the groups they are members of.tblGroupPermission holds only those people who have permission to makechanges to another part of the DB.The SQL at the end of post works, but opens a dialogue box looking fora parameter value 'query1.ContactID'Clicking enter or cancel (without entering anything) works OK.What have I done wrong to cause this parameter request.Thanks ColinKSELECT tblGroupContact.ContactID, tblGroupContact.GroupIDFROM tblGroupContact LEFT JOIN tblGroupPermission ON(tblGroupContact.ContactID = tblGroupPermission.ContactID) AND(tblGroupContact.GroupID = tblGroupPermission.GroupID)WHERE (((tblGroupPermission.ContactI*D) Is Null) AND((tblGroupPermission.GroupID) Is Null));

View 3 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

Select Records From Two Tables, Plus Page And Order Them?

Feb 7, 2008

Hi,

I'm trying to look for an efficient way to select records from two tables, combine them (not just one set above the other) and also efficiently page the results as well as dynamically order by specific columns. So far I have this....

DECLARE @Temp TABLE
(
IDINTNOT NULL,
NameNVARCHAR(128)NOT NULL,
TypeCHAR(1)NOT NULL
)

INSERT INTO @Temp
SELECT i.ID, i.Name, Type = 'I' FROM Item i
UNION
SELECT p.ID, p.Name, Type = 'P'FROM Package p

SELECT * FROM @Temp
ORDER BY Name ASC

I was going to then implement some sort of of ROW_NUMBER like paging and ordering on the @Temp table variable. Problem is there could be potentially 1000's or more Items and Packages and they would all go in this single Temp table before having records 1 to 10 returned. Is there a more efficient way of doing this before I proceed any further?

Many thanks for any help! :)

View 10 Replies View Related

Transact SQL :: Select Records From Two Child Tables

Jun 18, 2015

i have 3 tables names parent, child1, child2 parent has 1 record, child1 has 2 record and child 3 has 3 records the script

select Parent.*,child1.f1,child2.f2 from child1 inner join Parent on parent.id =child1.id
inner join child2 on child1.id =child2.id

running above query gives me sixes rows but i want only all rows of childs but not their Cartesian products

Object: Table [dbo].[Parent] Script Date: 06/18/2015 17:33:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Parent](
[id] [int] NOT NULL,

[code]....

View 8 Replies View Related

Why I Am Not Able To Select Records Against The Temparary Tables From Tempdb Database?

Jun 25, 2007

Hi, all experts here,

I encontered a problem which did not allow me to select any thing from the temparary tables of the tempdb database. (e.g one of the temparary tables is #239E4DCF), why is that and how can we solve this problem? As I urgently need to look at this tempdb database, it is full. I am looking forward to hearing from you and thanks a lot in advance for your help.

With best regards,

Yours sincerely,

View 1 Replies View Related

Trying To Get Distinct Records

Jan 7, 2008

How do I get distinct TitleID and Titles? Right now I'm still getting duplicates on them both. Here's my stored procedure.
select Distinct (Titles.Titleid), Titles.Title as TITLE, classifications.[description]as TOPIC,Titles.descriptions,media.[description] as MEDIA
from Titlesjoin resources on resources.Titleid = Titles.Titleidjoin media on media.mediaid = resources.mediaidjoin titleclassification on titleclassification.titleid = titles.titleidjoin classifications on classifications.classificationid = titleclassification.classificationid WHERE Title LIKE 'p' + '%' GROUP BY Titles.titleid, titles.title,classifications.[description],Titles.[descriptions],media.[description]
Thanks!

View 18 Replies View Related

Distinct Records

May 11, 2008

hi,
i want to ask a simple question,
i have a sql server relational tables named "tbl_Contact" (w/c has a field of ID & ContactName) and "tbl_reviews" (w/c has ID,ContactCode & Reviews).
the tables relationship is one-to-many, now my question is, how can i display a single record per 'ContactCode' from 'tbl_review'??
thanks..

View 7 Replies View Related

Only Get The Non Distinct Records

Oct 1, 2007

I am trying to write a query that will only return the non distinct records

what i have is a bunch of line items that have an order id and a rate

there may be 10 line items with the same orderid, I need to get the orderid and rates for the orderids that have more than one rate

how would i do this?

View 15 Replies View Related

Getting Distinct Records

Oct 16, 2007

how do i get an entire record returned when looking for only column to be unique?

basically i have 3 columns

id, tranid, appliedtranid

tranid can have multiple appliedtranids

i need to grab the top 10 distinct tranids but i need the entire record

View 4 Replies View Related

Get Top Distinct Records

Jan 31, 2008

hi I am trying to write a query that will return the most recent status of an event

my query is below

SELECT DISTINCT eventname, MIN(Status) AS Expr1, MIN(RecordUpdated) AS Expr2
FROM EventQueue
GROUP BY AuditName

does this query always result with the most recent distinct eventname record?

I need to make sure that the recordupdated is the most recent for the eventname

View 2 Replies View Related

Selecting Distinct Records

Sep 1, 2007

Hi,
I'm just wondering if someone can help me with some SQL syntax stuff.I want to take this sql statement:
"SELECT TOP 50 tblProfile.chName, tblProfile.intCount FROM tblProfile, tblLinks WHERE (tblLinks.MemberID = tblProfile.MemberID) ORDER BY tblLinks.dtDateAdded DESC;"
and select only unique "chName's" records

View 9 Replies View Related

Display Distinct Records?

Dec 7, 2011

TableName: Order_Archive
Fields:
orderid
load_date
filename
order_date
dollar

I load a file each week into a table, each file has unique orderid, load_date, filename, order_date and dollar. However the same orderid, order_date and dollar could appear in another file with different load_date and file_name.

File1:
orderid, load_date, file_name, order_date, dollar
'1000', '2011-01-01', 'File1', '2011-01-01', '101'
'1001', '2011-01-01', 'File1', '2011-01-01', '102'
'1002', '2011-01-01', 'File1', '2011-01-01', '103'

File2:
orderid, load_date, file_name, order_date, dollar
'1001', '2011-01-08', 'File2', '2011-01-01', '102'
'1002', '2011-01-08', 'File2', '2011-01-01', '103'
'1003', '2011-01-08', 'File2', '2011-01-01', '104'

Question:
whats is the best way to retrieve the distinct records that has the most recent load_date? expected results below:

Expected Results:
orderid, load_date, file_name, order_date, dollar
'1000', '2011-01-01', 'File1', '2011-01-01', '101'
'1001', '2011-01-08', 'File2', '2011-01-01', '102'
'1002', '2011-01-08', 'File2', '2011-01-01', '103'
'1003', '2011-01-08', 'File2', '2011-01-01', '104'

View 3 Replies View Related

How To Get Distinct Records In Table

Dec 26, 2013

For examble:

In a table name like

1.Test
2.Test.
3.Test..
4.Test...

How will get the name "Test" using distinct keyword?

View 1 Replies View Related

Count Distinct Records

May 15, 2008

how can i count in sql the number or records taht would be returned if i did

select distinct site,date from allrecords

View 1 Replies View Related

How To Get Distinct Records In Table

Dec 25, 2013

For examble:

In a table name like

1.Test
2.Test.
3.Test..
4.Test...

How will get the name "Test" using distinct keyword?

View 2 Replies View Related

DISTINCT And Number Of Records

Mar 19, 2008

Wondering if there is a way to do this...

I want to select the DISTINCT user name from each record in a table, and then have another field tell me how many records it found..

is there any way to do this with SQL?

SELECT DISTINCT user_Name FROM table ORDER BY user_Name

is what I have now....

need something that will return:

user_Name numRecs
bob 3
fred 6
sam 1
linda 2

View 1 Replies View Related

Selecting Distinct Records Through CTE

Jan 11, 2013

I have a table contains information related to sales:

SO number Order Date     Customer SellingPerson
1001          2012/07/02     ABC          Andy
1002          2012/07/02     XYZ           Alan
1003          2012/07/02     EFG          Almelia
1004          2012/07/02     ABC John
1005 2012/07/02     XYZ    Oliver
1006          2012/07/02     HIJ           Dorthy
1007          2012/07/02     KLM          Andy
1008          2012/07/02     NOP         Rowan
1009          2012/07/02     QRS          David
1010          2012/07/02     ABC          Joey

Now, i want to write a query using CTE that gives me first five distinct customer in result set:

SO number Order Date     Customer SellingPerson
1001          2012/07/02     ABC          Andy
1002          2012/07/02     XYZ           Alan
1003          2012/07/02     EFG          Almelia
1006          2012/07/02     HIJ           Dorthy
1007          2012/07/02     KLM          Andy

I wrote this query :

With t(so_number,order date,customer, SellingPerson)
as
(select top 5 so_number,order date,customer, SellingPerson from t)
select distinct billingcontactperson from t order by so_id

And getting this error:

Msg 252, Level 16, State 1, Line 1
Recursive common table expression 't' does not contain a top-level UNION ALL operator.

View 9 Replies View Related

Selecting Distinct Records Quickly

Mar 26, 2007

Good day,

I have a table of approximately 10 million rows. The table has 3 field making up the key, namely:
ID, Date, Program

I need to extract all the distinct Program's from the table.
I have don so with:
Select distinct Program from table
This unfortunately takes roughly 2 minutes which is far to long. Is there something I can do to help speed this process up?

Thanks in advance.

View 14 Replies View Related

T-SQL (SS2K8) :: How To Get Distinct Records From Table

Apr 14, 2014

Suppose I have 2 table

1)Main
2)History

Main table maintain all the records having columns MAIN_SKU,DEDUCTIBLE_AMT,model_id,catagory,ModifiedDate

IF DEDUCTIBLE_AMT is changes it will place entry in history table ,columns are same with history_id

i want to display distinct main_sku from history table(all columns) with last DEDUCTIBLE_AMT changed from history table

table structure
main table
MAIN_SKUDEDUCTIBLE_amtmodel_idcatagory
1100100phone
2150101phone
3200109smartphone
4100202smartphone
History table
History_idMAIN_SKUDEDUCTIBLE_amtmodel_idcatagoryModifiedDate
11150100phone4/14/2014
21200101phone4/13/2014
34109202smartphone4/14/2014
44101202smartphone4/13/2014
52200101phone4/13/2014
63100109smartphone4/12/2014

View 3 Replies View Related

Making Duplicate Records Distinct

Nov 25, 2014

I have some duplicate records in my table, I need to make those records so they are not duplicate. There is a small dateTime field in that table, I want to add seconds to each row of that field in sequential order so that the record becomes distinct so for e.g if I have this in the field

22-04-2014 10:00:00
22-04-2014 10:00:00
22-04-2014 10:00:00
22-04-2014 10:00:00

So I want to make it like this so that all the duplicate rows becomes distinct

22-04-2014 10:00:01
22-04-2014 10:00:02
22-04-2014 10:00:03
22-04-2014 10:00:04

I am not sure if it is possible, but if it is then how can I achieve this. There are around 20 columns in this table or if there is any other way to make these record distinct.

View 1 Replies View Related

Trying To Return Whole Records With Distinct Fields

Oct 17, 2006

Hi

I am trying to write a query that will return a full record with a particular distinct field (the rest of the record being the first such record that includes the distinct field).

For example, for the following:

Fruit Like? Colour
Apple Y Green
Orange N Orange
Banana Y Yellow
Grape Y Green
Grapefruit N Yellow

I would want to return (assuming Colour was the distinct field):

Fruit Like? Colour
Apple Y Green
Orange N Orange
Banana Y Yellow

How do I do this? I've tried using a join (of all different kinds) with a subquery that uses SELECT DISTINCT but this doesn't seem to work. I've tried GROUP BY but none of the aggregate functions seem to just take the first found field.

Thanks for any help you can offer.

View 11 Replies View Related

Selecting The Newest Records For Distinct Users

Oct 5, 2007

I have the following Table:DataIDuserIDTimeStampI need to create a query that selects the "newest" records for all distinct users. I'm hoping to get this done in one query. I know I could write multiple queries to find all distinct users, and then the newest records based on that... and so on... but I'm not sure that's the most efficient. I have the following so far, but is it the most efficient (I have indexing on most of my columns)? SELECT DATA.DataID, DATA.UserID, UN.vchName, UN.vchImage, U.vchFirstName + ' ' + U.vchLastName AS 'FullName' FROM [tblData] DATA INNER JOIN [tblUnits] UN ON DATA.UserID = Un.UserID INNER JOIN [tblusers] U ON U.UserID = UN.UserID WHERE GPS.intGPSDataID IN (SELECT MAX([tbLData].DataID) FROM [tbLData] GROUP BY [tbLData].UserID)

View 4 Replies View Related

Help With Getting Distinct Records From Multiple Columns In SQL Query

Nov 3, 2005

I'm exporting the following query to a datagrid, however in the result set, some values are duplicated (for various reasons... mostly old software and poor categorization)...On the records with identical values, I want to look at the account number and the DateOfService fields and search for joint distinct values and only display that...Current Example: ACCT NUM | DATE OF SERVICE |________________________________ 43490 | 10/01/2006 08:15:23 | 35999 | 10/10/2005 12:00:00 | 35999 | 10/24/2005 12:45:30 | 35999 | 10/10/2005 12:00:00 | 35999 | 10/10/2005 12:00:00 | 23489 | 10/15/2006 15:13:23 |Desired Result: ACCT NUM | DATE OF SERVICE |________________________________ 43490 | 10/01/2006 08:15:23 | 35999 | 10/10/2005 12:00:00 | 35999 | 10/24/2005 12:45:30 | 23489 | 10/15/2006 15:13:23 |Here is the query I'm working with... just can't figure out how to join or limit the results to ONLY unique matches in Acct Number AND DateOfService. "SELECT tblCH.ProcedureKey AS CPT, tblPC.Description, DATEDIFF(d, tblPat.BirthDate, " & _ " { fn NOW() }) / 365 AS Age, tblPat.LastName, tblPat.FirstName, tblPat.BirthDate," & _ " CAST(tblCH.AccountKey AS varchar) + '.' + CAST(tblCH.DependentKey AS varchar) AS Account, tblCH.DateOfService " & _ " FROM dbo.Procedure_Code___Servcode_dat tblPC INNER JOIN " & _ " dbo.Charge_History___Prohist_dat tblCH ON tblPC.ProcedureKey = tblCH.ProcedureKey RIGHT OUTER JOIN " & _ " dbo.Patient_Info___Patfile_dat tblPat ON tblCH.AccountKey = (tblPat.AccountKey AND tblCH.DependentKey) = tblPat.DependentKey "Any suggestions from y'all SQL gurus? I have to have this report ready for production by tomorrow morning and this is the last fix I need to make =Thank you =)

View 6 Replies View Related

Selecting Distinct Rows ?? From Over 10 Mill Records

Oct 27, 1998

hi, I have a table that contains 11,169,000 rows that was downloaded from the main frame. There are alot of duplicate records in that table. I ran a query select * from tbl.... it still running and running and running ... it never stoped.... what seems to be the problem.... There are no primary keys or index in that table...
so my question , how would I deal with such table ... I want to run certain reports from that table and it seems that all my attempts failed? anyone can help

View 5 Replies View Related

How To Copy Distinct Records From One Table To Another Using SQL 2005

Oct 24, 2007

I have a table on an offline database having let say 2000 rows and same table on an online database with let say 3000 rows. Now I want to copy just those records (1000) from online database that are not in the offline database table. Also, both tables have same name, same schema etc.

View 3 Replies View Related

Count Distinct In Large Table With 30M Records?

Jul 7, 2015

I have a task to count distinct records in a big table with roughly 30M records, performance is an important factor. Query is to be written to calculate weekly stats, weekly record number could be as high as 1M.

The actual result is like:
ID Policy
350235744Credit Cards
350235744PCI
350235744PCI Audit

So the final number for this particular Policy is 3

I can write the query like:

select count(distinct Incident_id) policy_name
from Reporting_DailyDlpDetail
Where (year(INSERT_DETECT_TS)=2015) and (month(INSERT_DETECT_TS) =6) and (day(INSERT_DETECT_TS) between 2 and 9)
This returns 526254 and costs 11 seconds to complete

or a query like:
Select distinct Incident_id, policy_name
from Reporting_DailyDlpDetail
Where (year(INSERT_DETECT_TS)=2015) and (month(INSERT_DETECT_TS) =6) and (day(INSERT_DETECT_TS) between 2 and 9)
This returns 749687 and costs roughly 1 minute to complete.

Result is different from the two queries, I believe the later gives correct number. How can I count the distinct based on a combo?Considering the size of data, what is the best and most efficient way to run the stats calculation against over 30 different scenarios (different policies and alert types) and not timeout?

View 9 Replies View Related

Transact SQL :: Get Only Those Distinct Records Which Satisfied Condition

Jun 3, 2015

I need to write a query on a table..

table defination .. table Client_Master
file_id int identity(1,1) primary key
, client_id int
,LOAN_SANCTION_DATE datetime
,is_reject bit

has Data ..

FILE_ID CLIENT_ID
LOAN_SANCTION_DATE IS_REJECT
21139 22784
2014-02-03 00:00:00.000 0
21141 22784
2014-02-03 00:00:00.000 NULL
20869 22784
2014-02-03 00:00:00.000 1
20870 22784
2014-02-03 00:00:00.000 0
21571 22784
2014-02-03 00:00:00.000 1
66056 22784
2014-02-03 00:00:00.000 1

Above has 6 files entries for client id 22784 and LOAN_SANCTION_DATE 2014-02-03 out of which 3 are rejected ..

Now , i want to write a query to select those distinct client_id , LOAN_SANCTION_DATE from Client_Master where all files has been rejected ..

means by grouping client ID and LOAN_SANCTION_DATE all the files are rejected ..

I have wrote as below .. got the result but not satisfy with the query

SELECT DISTINCT CLIENT_ID,LOAN_SANCTION_DATE,COUNT(FILE_ID) AS No_Of_Files
,COUNT(DISTINCT CASE WHEN IS_REJECT=1 THEN FILE_ID END )AS No_Of_Rejected
FROM dbo.FILE_MASTER
GROUP BY CLIENT_ID ,LOAN_SANCTION_DATE
HAVING
COUNT(FILE_ID)=COUNT(DISTINCT CASE WHEN IS_REJECT=1 THEN FILE_ID END )

View 4 Replies View Related