Comparing Similar Tables - Removing Duplicate Or Repeated Data

Sep 30, 2007

It seems that there should be a solution for my situation, but for the life of me I can't seem to figure it out.

I need to compare two "like" tables, containing similar data. Tbl 1 is "BOOKED" (which is a snapshot of inventory) and tbl 2 is "CURRENT" (the live - working inventory table). If I write my query as follows the the subsequent result is "duplicate" data.




Code Block
SELECT booked.item, booked.bin, booked.quantity, current.bin, current.quantity
FROM BOOKED
LEFT JOIN
CURRENT
ON booked.item = current.item







No matter what type of join I use, there is duplicate data displayed for each table. For example, if there are more bins in the BOOKED table that contain a certain product then the CURRENT table will repeat data and vica versa.

As follows:







Item
Bin
Quantity
Bin
Quantity

12345
A01
500
A01
7680

12345
B01
6
A01
7680

12345
C01
20
A01
7680

54321
G10
1032
E15
1163

54321
G10
1032
F20
523

54321
G10
1032
H30
750

98765
Z20
7000
Z20
8500

98765
Y15
2500
Y15
3000

98765
X10
1200
Y15
3000

What I would like to do is display Bin and Quantity only once and the repeating values as NULL or [BLANK]. Or, to display all of the bins from both tables and only the quantities from each table in relation to the bin found in that table, returning a "0" if no quantity exists.

This is what I'm after:







Item
Bin
Quantity
Bin
Quantity

12345
A01
500
A01
7680

12345
B01
6
B01
0

12345
C01
20
C01
0

54321
G10
1032
E15
1163

54321
F20
0
F20
523

54321
H30
0
H30
750

98765
Z20
7000
Z20
8500

98765
Y15
2500
Y15
3000

98765
X10
1200
X10
0



Is this possible? If so, how?

I also might add that it is ok for each table to contain multiple entries for any given item. This is basically being requested as an inventory variance report - inventory before physical count and immediatly after physical count - and will only be run once a year.

-----------------------------------------------
Just thinking out loud here:
What if I created three subqueries, the first containing only BOOKED information, the second containing only CURRENT information and the third being a UNION of both tables? Something like this:




Code Block
SELECT q3.bin, q1.item, ISNULL(q1.quantity, 0) as QTY_BEFORE, ISNULL(q2.quantity, 0) as QTY_AFTER

FROM

(select item, bin, quantity
from BOOKED)q1
Left Join

(select item, bin, quantity
from CURRENT)q2
on q1.item = q2.item
Left Join

(select bin, item
from BOOKED
UNION
CURRENT)q3
on q1.item = q3.item

Order By q1.item





I don't know if I wrote the UNION statement correctly, but I will have to try this when I get back to work...


Any suggestions?

View 7 Replies


ADVERTISEMENT

Comparing 2 Similar Columns In Different Tables

Mar 5, 2014

I am trying to write an SQL command for my crystal report. I need to compare the same column in 3different tables & get the data from each table for only the matching data.. I understand I need to create a temporary table, get the data into it & then work around.. I am quite new to SQL.

Eg: Considering one customer account
Table 1
Cust.No Name Amt_Counter AmtPaid
123.456 sam 0 0
123.456 sam 1 50

Table 2
Cust.No Name Freq_Counter Frequency
123.456 sam 1 0
123.456 sam 2 15

[code]....

View 3 Replies View Related

Removing Rows With Repeated Data

Sep 7, 2005

I am running a query on multiple tables and the data I get back consists of several repeated rows but with one column different. I want to take out those repeated rows and for the column that is different join that data and separate it by a comma. Can this be done?

Ex.
Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 717
Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 610
Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 310

So i would like this data to come up as:
Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 717,610,310

View 7 Replies View Related

How To Basically Copy Tables With New Names Rather Than Create Similar Tables From Similar Manual Input.

May 26, 2007

I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?Thanks in advance. 

View 4 Replies View Related

How Do I Copy Data From Similar Tables Knowing Unique ID Fields

Jul 20, 2005

I have two tables in my database called CartItems and OrderItems. Istore all of a session's shopping cart items in the CartItems tableusing the sessionID as the identifier (called cartID in my DB). Afteran order is placed and is approved, I would like to copy all of theitems in the CartItems table for that given cartID to the OrderItemstable given a new orderID.I will know the cartID and orderID ahead of time and would like tosend them both into a stored procedure and have the transfer takeplace.Example:take this data...CartItems (table)--------------------------------------cartID | itemID | quantity | price--------------------------------------12345 2 1 12.9512345 7 2 17.95and make it this data...OrderItems (table)--------------------------------------orderID | itemID | quantity | price--------------------------------------00001 2 1 12.9500001 7 2 17.95via some stored procedure that I send (@cartID,@orderID)Any help would be greatly appreciated!!

View 5 Replies View Related

Comparing Data Between Two Tables...

Jul 23, 2005

I would like to compare data across two tables. I have partinformation in a table. I get a new set of information periodically.I would like to compare my new info to my old info. I recognize thatdoing a compare of every attribute of every part will take FOREVER. Isthere some way I can do a "diff" based on the columns that I careabout?Thanks!--gloria

View 2 Replies View Related

Comparing Data In 2 Tables

Jan 15, 2008



I need to compare two tables in two different databases and get data that is only in one table.

Table 1 in DB1 and Table A in DB_A

I need all distinct IDs in Table 1 in DB1 that are NOT in Table A in DB_A
and copy those IDs into Table A.

Is the sql below correct in obtaining data?
Can I add the copy part also to this?



select ID

from DB1.dbo.Table1 aaa

where not exists ( select empID


from DB_A.dbo.TableA bbb
where aaa.ID = bbb.empID)

View 5 Replies View Related

Comparing Data In Tables/views.

May 13, 2008

I am looking for an efficient mechanism to compare data between 2 tables/views.

Rationale:
I use a proprietary tool to data transfer between 2 databases. The tool itself uses Microsoft SSIS (integration service) to transfer data. I want to make sure that the data is transfered properly. Both the source and target database are not live database. The source and target database are in seperate servers.

View 2 Replies View Related

Comparing Data And Integrity Between Two Tables

Jun 4, 2008

Hi everyone,

I have recently converted my DTS packages to SSIS and deployed them to the new server. I have the 2000 and 2005 server running concurrently, all that is left for me to do is compare the the tables generated by the DTS and SSIS packages to see if they are the same.


How do I go about comparing the tables, which are from two different servers using SQL server 2005?



Thank you inadvance:)

Comparing data and integrity between two tables

View 2 Replies View Related

Comparing Data In Two Tables To Find Missing Records

Jul 20, 2005

I have two tables of book information. One that has descriptions of thebook in it, and the isbn, and the other that has the book title,inventory data, prices, the isbn.Because of some techncal constraints I won't get into now, I can'tcombine them both into one table. No problem. Things are going fine aslong as there is a description in the one table to corrispond to theisbn and other data in the other table.However, about half of the products are not yet entered into thedescrition table. I'd like to run a sql query that pulls up all theisbns that don't exist in the other. In other words, I'd like to get aquery that tells me exactly which isbns do not yet have descrition datain them. I know there is some sql that says to search from one filewhere the number does not exist in the other, but it slips my mind. Cansomeone help me on this please?Thank you!Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 2 Replies View Related

SQL Server 2012 :: Removing / Deleting Data From 2 Tables

Mar 11, 2015

I have found a bunch of duplicate records in our housing database that ideally I need to delete.There are two tables that I need to remove data from ih_cml_log_entry and ih_cml_log_notes. There is no unique identifier between the tables for a log entry. So I have had to join on the person_ref, log_seq and the date/time of entry.How do I go about deleting the data - I've used the script below to identify what I need to delete -

SELECT *
FROM
(
select cml.person_ref, cml.open_date + open_time as 'datetime',cml.open_user,cml.log_type
,ROW_NUMBER() OVER (PARTITION BY cml.person_ref, cml.open_date + cml.open_time,cml.open_user,cml.log_type ORDER BY (SELECT 0)) AS RowNo
,n.note
FROM ih_cml_log_entry cml

[code]...

View 2 Replies View Related

Search Repeated Record And Give Suggestion As Most Repeated One

Sep 4, 2007

I have problem, i wanted a query which will search the duplicate and then give suggestionmost repeated word
Table containing the records like below








ID 
Movie Name 
New Name

1
Spider Man 
Spider Man

2
Spider Man 2 
Spider Man

3
Spider Man 3 
Spider Man

4
Spider Man UK 
Spider Man

5
Spider Man USA 
Spider Man

6
New Spider Man 
Spider Man

7
Spider Man Black 
Spider Man

8
Spider Man Part 1 
Spider Man

9
Spider Man Part 2 
Spider Man

10
Spider Man I 
Spider Man

11
Spider Man III 
Spider Man

12
Spider Man Part II 
Spider Man
My manufacturer send me the data in this format and i have to allocate there new name
to do some comparison
I wanted to make this process automatic.
what i mean is i need a query which will give me a repeated records  along with suggestion
as new name.
I am fully confident that you  guys will help me out from this problem.
Looking forward

View 9 Replies View Related

In Few Tables We Have Duplicate Data?

Apr 30, 2008

How to fetch that replicated records?


Anyone can share the query??

View 2 Replies View Related

Removing Duplicate Value From One Column.

Feb 23, 2001

Hi all,
I have one table in which one column contains duplicate values. My question is how i can use T-SQL so that i can retrive values for all columns in the table which are distinct and retriving the single value from column which contains duplicate values.

(I know distinct)
Thanks in advance.

Minesh.

View 2 Replies View Related

Removing Duplicate Rows

Dec 3, 2005

Hi,

Please give the DML to SELECT the rows avoiding the duplicate rows. Since there is a text column in the table, I couldn't use aggregate function, group by (OR) DISTINCT for processing.

Table :

create table test(col1 int, col2 text)
go
insert into test values(1, 'abc')
go
insert into test values(2, 'abc')
go
insert into test values(2, 'abc')
go
insert into test values(4, 'dbc')
go

Please advise,

Thanks,
MiraJ

View 7 Replies View Related

Removing Duplicate Records

Dec 5, 2007

Dear All

This is query i have written is giving following output.

FormCodeRefCodeSerialnoDateTime
R1-196H1-68A12232138/6/2007 19:38:11
R1-196H1-68A12232138/6/2007 19:38:14
R1-205H1-67XS23124148/6/2007 19:36:08
R1-205H1-67XS23124148/6/2007 19:36:10
R1-220H1-66F433365348/6/2007 19:30:27
R1-220H1-66F433365348/6/2007 19:30:29
R1-400H1-64ER53436648/6/2007 19:24:23
R1-400H1-64ER53436648/6/2007 19:24:26
R1-408H1-65TE4626268/6/2007 19:24:23
R1-408H1-65TE4626268/6/2007 19:25:00


I want the output like this,it should take only Min Datecreated
FormCodeRefCodeSerialnoDateTime
R1-196H1-68A12232138/6/2007 19:38:14
R1-205H1-67XS23124148/6/2007 19:36:08
R1-220H1-66F433365348/6/2007 19:30:27
R1-400H1-64ER53436648/6/2007 19:24:23
R1-408H1-65TE4626268/6/2007 19:24:23

View 2 Replies View Related

Removing Duplicate Records

Jul 11, 2006

I have a table that holds the following

1 7530568 87143 OESCHD 1/5/2006 6:31:58 AM
1 7530568 87143 OESCHD 1/5/2006 7:02:36 AM

for each 7530568 ordernumber there should only be one OESCHD status.

This is the query I'm using to insert the data sent to me.

INSERT INTO ORDER_EVENTS
SELECT d.division as division,
dt.orderNum as orderNum,
dt.poNum as poNum,
dt.statusCode as statusCode,
dt.statusChangeDate as statusChangeDate
FROM dt_Order_Events dt INNER JOIN
division d ON dt.division = d.divisionShort INNER JOIN
status s ON s.division = d.division AND s.statCode = dt.statusCode
WHERE directive <> 'C' AND
dt.orderNum IN (SELECT orderNum FROM ORDER_HEADER)

This works fine when used with in the hourly transactional update. But When I ran it for the Bulk UpDate (so we'd have historical data) it allowed orders to have statuses to many times.

I am not a SQL guru, I have no idea how to write a sql statement or stored proc that will remove the duplicate records. or how to change what I have to prevent further ones.

Any help would be apreciated.

View 1 Replies View Related

Removing Duplicate In Listbox

Apr 23, 2008

Hi There,

I am having a 1st listbox which is populating production lines, 2nd combobox to populate the production units. From here , the user will be able to select multiple production units from the combobox and hence populate the variable in listbox 3.

This is the query that I'm using to query on the variable table:
'SELECT DISTINCT PU_Id,Var_Id,Var_Desc from Variables
where PU_Id IN (@ProductionUnits)'

The problem now is that this would return all the variables for the selected production units and for those variables that have the same name, they would appear in the listbox as duplicates.

I wonder if there's any way to filter off or remove the duplicates in the listbox 3 by using sql query?

Thanks in advance for the help.

ksyong17

View 9 Replies View Related

SQL: Removing Partial Duplicate Rows

Dec 24, 2007

Hi,
 I have results from a survey in a table, every entry is assigned a unique ID. I want to remove duplicate entries based on the survey data but not on the unique ID (obviously).
So far I have...SELECT DISTINCT RespondantID, Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8, Q9, Q10, Q11, Q12, Q13
FROM Results
But that gives...




1 - Anonymous
1
1
1
1
1
1
1
1
1
1
1
1
1

2 - Anonymous
2
2
2
2
2
2
2
2
2
2
2
2
2

3 - Martin
2
2
2
2
2
2
2
2
2
2
2
2
2
I.e. in the above example, it would seem that 'Martin' submitted his data once and then submitted it again with his name.
 How can I remove the '2 - Anonymous' frrom the data set?
 Thanks!

View 8 Replies View Related

Removing Duplicate Rows In Grouping

Feb 14, 2012

i've to generate a notepad using this query in vb.net :

strSql = "Select count(*), d.ShareholderId, d.UsufructId, d.BnkAccount, b.SBMCode, " & _
"LTRIM(ISNULL(d.TitleCode + ' ', '')) + LTRIM(ISNULL(d.Forename + ' ', '')) + d.Surname as ShName," & _
"d.BankCode, (select count(*) from (select ShareholderId from dividend " & _
"where CompCode = 'L1' and PaymentMode = 'B' group by ShareholderId, UsufructId, " & _

[Code]....

In the select statement i need to select "d.amount" as well. When i do so, it ask me to insert it in the group by option or in an aggregate function.

Grouping by "d.amount" returns extra field as there can be 2 similar "d.shareholderId" but with different amount.

how to group the "d.amount" without having repetition in the "d.shareholderid" ??

View 5 Replies View Related

Removing Duplicate Results With One Column Different?

Aug 9, 2012

I am trying to get people from my table that have closed accounts. However, in my table many people have more than one account. They will have multiple closed accounts and some active accounts. I need to get the people with only closed accounts.

Values in the table

Code:
name surname status Closed Number
----------- --------- ----------- ------------- ----------------------------
Jeff Burns closed 2012/01/01 142
Tina Drewmor closed 2008/05/20 546
Jeff Burns active 1900/01/01 354
Kyle Higgin active 1900/01/01 851
Tina Drewmor closed 2009/04/14 154

The query I am using so far is:

Code:
select
d.name,
d.surname,
s.status,
s.closed,
s.number
from
d d inner join s s on d.number = s.number
where
s.status = 'closed'

What I need to see in the results

Code:
name surname status Closed Number
----------- --------- ----------- ------------ -----------------------------
Tina Drewmor closed 2008/05/20 546
Tina Drewmor closed 2009/04/14 154

View 4 Replies View Related

Removing Duplicate Records With Criteria

Mar 9, 2015

I have regular work that requires me to extract a bunch of customer records from our database, and then remove duplicate address destinations (so we dont mail the same address more than once).

I can currently achieve this using a combination of my poor SQL skills and Excel, but it's really not working out for me, so looking for SQL wizardry necessary to do it just in SQL.

Relevant fields:
Member.AddressBarcode (This is a unique barcode (Text representation of a base-3 number) based on the customer address. So if there's more than one record in the pulled records with the same barcode, we then look at Member.MemberTypeID to determine whether to include this record in the results or discard it as a duplicate. Note that AddressBarcode may be blank if the mailing address couldn't be validated, if it is blank we don't discard it since there is no easy way to detect duplicate addresses without the barcode)

Member.MemberTypeID (This is the type of member account. We have 3 types - Single, Joint Primary, Joint Secondary, represented in this field by the numbers 1/2/3. This is also the order of preference of who to mail. So if there is a Joint Primary and Joint Secondary with the same mailing barcode, we want to discard the Joint Secondary from the results, so that the Joint Primary is the record we include in the results of who to mail.)

Member.ID (Unique numeric ID for each customer. Kind of irrelevant here, but it's a key)

So some pseudo code for what I'm trying to achieve is:

(Member.MemberTypeID = 1)
OR (Member.MemberTypeID = 2 AND Member.AddressBarcode not in results of Member.MemberTypeID = 1)
OR (Member.MemberTypeID = 3 AND Member.AddressBarcode not in results of Member.MemberTypeID = 2 AND Member.AddressBarcode not in results of Member.MemberTypeID = 1)

I suspect it requires some sort of join...

View 5 Replies View Related

Removing Duplicate Entries In SQL Field

Jan 3, 2007

Hi All,



Below is a snippet of MS SQL inside some VB that retieves
Commodity info such as product names and related information and returns the results in an ASP Page. My problem is that with certain searches, elements returned in the synonym field repeat. For instance, on a correct search I get green, red, blue, and yellow which is correct. On another similar search with different commodity say for material, I get Plastic, Glass,Sand - Plastic, Glass,Sand - Plastic, Glass, Sand. I want to remove the repeating elements returned in this field. IOW, I just need one set of Plastic, Glass and Sand. I hope this makes sense.


Below is the SQL and the results from the returned page.


PS I tried to use distinct but with no luck I want just one of each in the example below.

Thanks in Advance!

Scott

==============================

SQL = ""
SQL = "SELECT B.CIMS_MSDS_NUM," & _
"A.COMMODITY_NUMBER, " & _
"B.CIMS_TRADE_NME," & _
"B.CIMS_MFR_NME," & _
"B.CIMS_MSDS_PREP_DTE," & _
"B.APVL_CDE," & _
"COALESCE(C.REGDMATLCD,'?') AS DOTREGD," & _
"COALESCE( D.CIMS_TRADE_SYNM,'NO SYNONYMS') AS SYNONYM, " & _
"A.MSDS_CMDTY_VERIF, " & _
"A.CATALOG_ID " & _
"FROM ( MATEQUIP.VMSDS_CMDTY A " & _
" RIGHT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_INF B " & _
" ON A.CIMS_MSDS_NUM = B.CIMS_MSDS_NUM " & _
" LEFT OUTER JOIN MATEQUIP.VDOT_TRADE_PROD C " & _
" ON A.CIMS_MSDS_NUM = C.MSDSNUM " & _
" LEFT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_SYN D " & _
" ON B.CIMS_MSDS_NUM = D.CIMS_MSDS_NUM) "

SQL1 = ""
SQL1 = SQL

SQL = SQL & "WHERE " & Where & " "
==================================

Here is a piece of the problem field, note repeating colors etc.

CCM-PAINTS & COATINGS (1/26/98)F65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65B1 GLOSS BLACK F65N11

View 1 Replies View Related

Removing Non-useful Records And Duplicate DB Objects. ( Purging)

Oct 9, 2007



hi All,
I have to remove non-useful and duplicate records containing NULL , Blanks and extra spaces (either on left or right side of the column values) etc from all the tables in my server's DB XYZ weekly thru a a scheduled job with the help of a Stored Proc, that s i guess called Purging og DB. Plz help how i can do it with T-SQL.

Also i have to find out and remove all the duplicate DB objects(tables) from the DB .e.g. a table existing with name TABLE_TEST or TABLE_DEBUG etc for an original table TABLE , making sure no any of the base table is dropped.

Plz help me reagrding these two problems.

Thanks in advance for the quick replies.
Mohit

View 1 Replies View Related

Finding Duplicate Entries In A Smart Way - By Comparing First Two Words

Jul 20, 2005

What is the best way to compare two entries in a single table wherethe two fields are "almost" the same?For example, I would like to write a query that would compare thefirst two words in a "company" field. If they are the same, I wouldlike to output them.For example, "20th Century" and "20th Century Fox" in the companyfield would be the same.How do I do this? Do I need to use a cursor? Is it as simple as using"Like?"

View 2 Replies View Related

Removing Partially Duplicate Rows For Resultset ? Help Needed.

Feb 2, 2008

Hi, Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. Please provide separate solutions for SqlServer2000/2005. 
I have four tables namely – Forums,Topics,Threads and Messages  in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :- 
SELECT Forums.forumid AS ForumID,Topics.topicid AS TopicID,Topics.name AS TopicName,LastPosts.date as LastPostDate,LastPosts.author AS Author,
(select count(threadid) from Threads where Threads.topicid=Topics.topicid) as NoOfThreads
FROM Forums JOIN Topics ON Forums.forumid=Topics.forumid
LEFT OUTER JOIN
(
                 SELECT Topics.forumid,Threads.topicid,Messages.date,Messages.author FROM Topics
             INNER JOIN Threads ON Topics.topicid = Threads.topicid
                 INNER JOIN Messages ON Threads.threadid=Messages.threadid
             WHERE Messages.date=(SELECT MAX(date) FROM Messages WHERE Messages.threadid = Threads.threadid)
) as LastPosts
ON LastPosts.forumid = Topics.forumid AND LastPosts.topicid = Topics.topicid 
Whose result set is as below:- 




forumid
topicid
name
LastPostDate
author
NoOfThreads

1
1
Java Overall
2008-02-02 13:06:06.267
l@m.com
2

1
1
Java Overall
2008-01-27 14:46:41.000
c@b.com
2

1
2
JSP
NULL
NULL
0

1
3
EJB
NULL
NULL
0

1
4
Swings
2008-01-27 15:12:51.000
p@q.com
1

1
5
AWT
NULL
NULL
0

1
6
Web Services
NULL
NULL
0

1
7
JMS
NULL
NULL
0

1
8
XML,HTML
NULL
NULL
0

1
9
Javascript
NULL
NULL
0

2
10
Oracle
NULL
NULL
0

2
11
Sql Server
NULL
NULL
0

2
12
MySQL
NULL
NULL
0

3
13
CSS
NULL
NULL
0

3
14
FLASH/DHTLML
NULL
NULL
0

4
15
Best Practices
NULL
NULL
0

4
16
Longue
NULL
NULL
0

5
17
General
NULL
NULL
0 
I want the result set as follows:- 




forumid
topicid
name
LastPostDate
author
NoOfThreads

1
1
Java Overall
2008-02-02 13:06:06.267
l@m.com
2

1
2
JSP
NULL
NULL
0

1
3
EJB
NULL
NULL
0

1
4
Swings
2008-01-27 15:12:51.000
p@q.com
1

1
5
AWT
NULL
NULL
0

1
6
Web Services
NULL
NULL
0

1
7
JMS
NULL
NULL
0

1
8
XML,HTML
NULL
NULL
0

1
9
Javascript
NULL
NULL
0

2
10
Oracle
NULL
NULL
0

2
11
Sql Server
NULL
NULL
0

2
12
MySQL
NULL
NULL
0

3
13
CSS
NULL
NULL
0

3
14
FLASH/DHTLML
NULL
NULL
0

4
15
Best Practices
NULL
NULL
0

4
16
Longue
NULL
NULL
0

5
17
General
NULL
NULL
0  I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the message) from the Messages table as shown above. 
When I use the query by using only three tables namely Forums,Topics and Threads, I get the correct result. The query is as:- 
SELECT Topics.forumid AS ForumID,Forums.name AS ForumName,Topics.topicid as TopicID,Topics.name as TopicName,
LastPosts.author AS Author,LastPosts.lastpostdate AS LastPost,
(select count(threadid) from threads where Threads.topicid=Topics.topicid) as NoOfThreads
FROM Forums JOIN Topics ON Forums.forumid=Topics.forumid LEFT OUTER JOIN (
             SELECT Topics.forumid, Threads.topicid, Threads.lastpostdate, Threads.author FROM Threads
             INNER JOIN Topics ON Topics.topicid = Threads.topicid
             WHERE Threads.lastpostdate IN (SELECT MAX(lastpostdate) FROM threads WHERE Topics.topicid = Threads.topicid)
             ) AS LastPosts
             ON LastPosts.forumid = Topics.forumid AND LastPosts.topicid = Topics.topicid 
Whose result set is as:- 




ForumID
ForumName
TopicID
TopicName
Author
LastPost
NoOfThreads

1
Developers
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2

1
Developers
2
JSP
NULL
NULL
0

1
Developers
3
EJB
NULL
NULL
0

1
Developers
4
Swings
p@q.com
2008-01-27 15:12:51.000
1

1
Developers
5
AWT
NULL
NULL
0

1
Developers
6
Web Services
NULL
NULL
0

1
Developers
7
JMS
NULL
NULL
0

1
Developers
8
XML,HTML
NULL
NULL
0

1
Developers
9
Javascript
NULL
NULL
0

2
Database
10
Oracle
NULL
NULL
0

2
Database
11
Sql Server
NULL
NULL
0

2
Database
12
MySQL
NULL
NULL
0

3
Desginers
13
CSS
NULL
NULL
0

3
Desginers
14
FLASH/DHTLML
NULL
NULL
0

4
Architects
15
Best Practices
NULL
NULL
0

4
Architects
16
Longue
NULL
NULL
0

5
General
17
General
NULL
NULL
0  
The scripts for creating the tables and inserting test data is as follows in an already created database:- 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__35BCFE0A]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__35BCFE0A
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__34C8D9D1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__34C8D9D1
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Messages__thread__33D4B598]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Messages] DROP CONSTRAINT FK__Messages__thread__33D4B598
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Forums]
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Topics]
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Threads]
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Messages]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Messages]
GO 
CREATE TABLE [dbo].[Forums] (
            [forumid] [int] IDENTITY (1, 1) NOT NULL ,
            [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO 
CREATE TABLE [dbo].[Topics] (
            [topicid] [int] IDENTITY (1, 1) NOT NULL ,
            [forumid] [int] NULL ,
            [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO 
CREATE TABLE [dbo].[Threads] (
            [threadid] [int] IDENTITY (1, 1) NOT NULL ,
            [topicid] [int] NOT NULL ,
            [subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [replies] [int] NOT NULL ,
            [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [lastpostdate] [datetime] NULL
) ON [PRIMARY]
GO 
CREATE TABLE [dbo].[Messages] (
            [msgid] [int] IDENTITY (1, 1) NOT NULL ,
            [threadid] [int] NOT NULL ,
            [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [date] [datetime] NULL ,
            [message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO 
ALTER TABLE [dbo].[Forums] ADD
             PRIMARY KEY  CLUSTERED
            (
                        [forumid]
            )  ON [PRIMARY]
GO 
ALTER TABLE [dbo].[Topics] ADD
             PRIMARY KEY  CLUSTERED
            (
                        [topicid]
            )  ON [PRIMARY]
GO 
ALTER TABLE [dbo].[Threads] ADD
             PRIMARY KEY  CLUSTERED
            (
                        [threadid]
            )  ON [PRIMARY]
GO 
ALTER TABLE [dbo].[Messages] ADD
             PRIMARY KEY  CLUSTERED
            (
                        [msgid]
            )  ON [PRIMARY]
GO 
ALTER TABLE [dbo].[Topics] ADD
             FOREIGN KEY
            (
                        [forumid]
            ) REFERENCES [dbo].[Forums] (
                        [forumid]
            )
GO 
ALTER TABLE [dbo].[Threads] ADD
             FOREIGN KEY
            (
                        [topicid]
            ) REFERENCES [dbo].[Topics] (
                        [topicid]
            )
GO 
ALTER TABLE [dbo].[Messages] ADD
             FOREIGN KEY
            (
                        [threadid]
            ) REFERENCES [dbo].[Threads] (
                        [threadid]
            )
GO  
------------------------------------------------------ 
insert into forums(name,description) values('Developers','Developers Forum');
insert into forums(name,description) values('Database','Database Forum');
insert into forums(name,description) values('Desginers','Designers Forum');
insert into forums(name,description) values('Architects','Architects Forum');
insert into forums(name,description) values('General','General Forum'); 
insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall');
insert into topics(forumid,name,description) values(1,'JSP','Topic JSP');
insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans');
insert into topics(forumid,name,description) values(1,'Swings','Topic Swings');
insert into topics(forumid,name,description) values(1,'AWT','Topic AWT');
insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services');
insert into topics(forumid,name,description) values(1,'JMS','Topic JMS');
insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML');
insert into topics(forumid,name,description) values(1,'Javascript','Javascript');
insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle');
insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server');
insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL');
insert into topics(forumid,name,description) values(3,'CSS','Topic CSS');
insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML');
insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices');
insert into topics(forumid,name,description) values(4,'Longue','Longue');
insert into topics(forumid,name,description) values(5,'General','General Discussion'); 
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM'); 
insert into messages(threadid,author,date,message) values(1,'a@b.com','1/27/2008 2:44:39 PM','Where can I find Java tutorials.');
insert into messages(threadid,author,date,message) values(1,'c@d.com','1/27/2008 2:45:28 PM','Please visit www.java.sun.com');
insert into messages(threadid,author,date,message) values(1,'c@d.com','1/27/2008 2:46:41 PM','Do a Google serach for more tutorials.');
insert into messages(threadid,author,date,message) values(2,'x@y.com','1/27/2008 2:48:53 PM','Please provide some Beginner tutorials.');
insert into messages(threadid,author,date,message) values(3,'p@q.com','1/27/2008 3:12:51 PM','How many finds of layout managers are there?');
insert into messages(threadid,author,date,message) values(2,'l@m.com','2/2/2008 1:06:06 PM','Search on Google.');
 

View 5 Replies View Related

Removing Duplicate Delimited Values From A Single Column?

Jun 18, 2014

I have a stored procedure that returns a single row based on a parameter of employee ID. This particular procedure uses a CTE to traverse our org structure. One of the columns is returning a delimited string of Windows login values and due to the business rules, it can contain duplicate values. I need to have that column contain only unique values - no dupes.

For example, this one column could contain something like this:

domainuser1;domainuser2;domainuser2;domainuser 3;

The need is to convert to this:

domainuser1;domainuser2;domainuser3;

I know that's a tall order.

View 1 Replies View Related

SQL Server 2012 :: Removing Duplicate Character From String

Oct 18, 2015

How to write a function to remove duplicated characters only if they come in sequence.

Examples

darrk should return dark

eeagle should return eagle

redd should return red

corner should corner as it is as the r's are not in sequence.

View 9 Replies View Related

A Basic Question: Removing Duplicate Results From Max Function

Jun 10, 2006

Hi,Say I have a table Job with columns name, date, salary . I want to getthe name ,date and salary for the date when that person earned maximumsalary. I am using something likeSELECT X.name,X.date,X.salaryFROM job XWHERE X.salary IN(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);The problem is ; if a person earns maximum salary on two dates, both ofthe dates are printed. I just want to get any one of those two rows.I triedSELECT X.name,Min(X.date),X.salaryFROM job XWHERE X.salary IN(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);but it gives error.Can anybody please suggest a solution?Regards,Aamir

View 4 Replies View Related

Removing Duplicate Rows With CTE And Partition - Need Most Recent Entry

Sep 28, 2007

I have tested the code below to remove duplicate table entries based on the field IntOOS. This code works; however, I want to ensure I am removing the oldest entries and keeping the most recent entry. The field to key on this would be something like Max(ID). What would be the best way to ensure I keep the most recent table entry?


/*** Removes duplicate rows from ampfm.rpt_AdtVisitDiag table

by Chuck King 20070928

***/

;WITH CTE

as

(

SELECT

ROW_NUMBER() OVER (Partition By IntOOS Order BY IntOOS) AS ROWID

,DischDate

,AdmDiagCode

,ID

,AdmDiagCodeDesc

,AdmittingDiagnosis

,AnyDx

,DischDx

,ECode

,IntOOS

,PrinDxAnesDesc

,PrinDxAnesInd

,PrinDxCode

,PrinDxCondDesc

,PrinDxCondInd

,PrinDxDesc

,PrinDxEqualsAdmDxYnu

,SecDx10AnesDesc

,SecDx10AnesInd

,SecDx10Code

,SecDx10CondDesc

,SecDx10CondInd

,SecDx10Description

,SecDx11AnesDesc

,SecDx11AnesInd

,SecDx11Code

,SecDx11CondDesc

,SecDx11CondInd

,SecDx11Description

,SecDx12AnesDesc

,SecDx12AnesInd

,SecDx12Code

,SecDx12CondDesc

,SecDx12CondInd

,SecDx12Description

,SecDx13AnesDesc

,SecDx13AnesInd

,SecDx13Code

,SecDx13CondDesc

,SecDx13CondInd

,SecDx13Description

,SecDx14AnesDesc

,SecDx14AnesInd

,SecDx14Code

,SecDx14CondDesc

,SecDx14CondInd

,SecDx14Description

,SecDx15Code

,SecDx15Description

,SecDx1AnesDesc

,SecDx1AnesInd

,SecDx1Code

,SecDx1CondDesc

,SecDx1CondInd

,SecDx1Description

,SecDx2AnesDesc

,SecDx2AnesInd

,SecDx2Code

,SecDx2CondDesc

,SecDx2CondInd

,SecDx2Description

,SecDx3AnesDesc

,SecDx3AnesInd

,SecDx3Code

,SecDx3CondDesc

,SecDx3CondInd

,SecDx3Description

,SecDx4AnesDesc

,SecDx4AnesInd

,SecDx4Code

,SecDx4CondDesc

,SecDx4CondInd

,SecDx4Description

,SecDx5AnesDesc

,SecDx5AnesInd

,SecDx5Code

,SecDx5CondDesc

,SecDx5CondInd

,SecDx5Description

,SecDx6AnesDesc

,SecDx6AnesInd

,SecDx6Code

,SecDx6CondDesc

,SecDx6CondInd

,SecDx6Description

,SecDx7AnesDesc

,SecDx7AnesInd

,SecDx7Code

,SecDx7CondDesc

,SecDx7CondInd

,SecDx7Description

,SecDx8AnesDesc

,SecDx8AnesInd

,SecDx8Code

,SecDx8CondDesc

,SecDx8CondInd

,SecDx8Description

,SecDx9AnesDesc

,SecDx9AnesInd

,SecDx9Code

,SecDx9CondDesc

,SecDx9CondInd

,SecDx9Description

,VisitTypeCode

,accountnumber

,DischVisitTypeCode

FROM ampfm.rpt_AdtVisitDiag

)

--Select * From CTE

Delete From CTE Where ROWID > 1

View 5 Replies View Related

Transact SQL :: Removing Duplicate Values In ID Column Of Table

Oct 14, 2015

I need write a query for removing duplicates, for Example in my table I have columns

A_ID name id
1 sam 10
2 sam 10
3  sam 10
4 sam 10
5  ccc 15
6 ccc 15
7  ccc 15
8 fff 20
9 fff 20
10 fff 20

So now I have duplicates values in id column so now I need to take only one value of each and delete the remaining. I need to take first id value 10,15,20 so only 3 rows should be there in my table.

View 4 Replies View Related

Add Similar Fields To Many Tables

Dec 13, 2004

Folks, i have to create four fields in every user table within my database:

CREATED_BY VARCHAR(25), CREATED_DATE [DATETIME], MODIFIED_BY VARCHAR(25), MODIFIED_DATE [DATETIME]

There are more than hundred tables, so i wanna automate this. i am tryin to do this in a cursor: please guide!

declare @name VARCHAR (50)
declare cur cursor
fast_forward
for select name from sysobjects where type='u' and status not like '-%'
open cur
WHILE (1=1)
BEGIN
FETCH NEXT
FROM cur
INTO @name
IF @@fetch_status = 0
BEGIN
ALTER TABLE @name
ADD created_by [VARCHAR] (25)
GO
ALTER TABLE @name
ADD created_by [VARCHAR] (25)
GO
ALTER TABLE @name
ADD created_date [DATETIME]
GO
ALTER TABLE @name
ADD modified_by [VARCHAR] (25)
GO
ALTER TABLE @name
ADD modified_date [DATETIME]
END
ELSE
BREAK
END
DEALLOCATE cur


I also want that if one column for a table exists; the other columns should be created rather than it quits.


Howdy!

View 4 Replies View Related

How To Go About Adding Similar Tables?

May 26, 2007

I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.

Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.

I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.

I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.

How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?

Thanks in advance.

View 5 Replies View Related







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