Help With Select To Return Duplicate Rows
Mar 30, 2007
Can someone look at this and tell me where I went wrong? I'm trying to return all duplicate rows that have the same lastName and Address. It returns rows but they don't look like dups.SELECT TOP (100) PERCENT dbo.tblClient.LastName, dbo.tblClientAddresses.Address
FROM dbo.tblClient INNER JOIN
dbo.tblClientAddresses ON dbo.tblClient.Client_ID = dbo.tblClientAddresses.Client_ID
GROUP BY dbo.tblClient.LastName, dbo.tblClientAddresses.Address
HAVING (COUNT(dbo.tblClientAddresses.Address) > 1)
ORDER BY dbo.tblClientAddresses.Address
View 4 Replies
ADVERTISEMENT
Nov 28, 2007
Dear Gurus,I have table with following entriesTable name = CustomerName Weight------------ -----------Sanjeev 85Sanjeev 75Rajeev 80Rajeev 45Sandy 35Sandy 30Harry 15Harry 45I need a output as followName Weight------------ -----------Sanjeev 85Rajeev 80Sandy 30Harry 45ORName Weight------------ -----------Sanjeev 75Rajeev 45Sandy 35Harry 15i.e. only distinct Name should display with only one value of Weight.I tried with 'group by' on Name column but it shows me all rows.Could anyone help me for above.Thanking in Advance.RegardsSanjeevJoin Bytes!
View 4 Replies
View Related
Feb 11, 2004
I get a tabseparated textfile with data every friday. Faulty rows are to be returned to the source.
Double rows are considered a fault. How do I select all double rows?
I join the data later on with four columns so if these columns are alike the rows are considered identical even if other columns are not. Ie:
A, B, C, D, 12, 34, 48, 76
A, B, C, D, 23, 45, 56, 99
These two rows are considered alike since I join on A+B+C+D.
I'd like to select ALL double rows and insert them into a separate table that I can return to the source. Does anyone have an idea how to do this?
View 10 Replies
View Related
Mar 25, 2008
I have a table with four columns: id, value1, value2, and name. I need to select all duplicate rows with the same name in the name field. Once selected I need to average the value1 and value2 rows with each other. Then I would like to delete the duplicate rows and replace with the new averaged values. Should I first move the duplicate rows to a temp table and then average? Any help would be appreciated.
View 1 Replies
View Related
Sep 13, 1999
Hi..,
There r plenty of duplicate rows in the legacy system(Unix /Cobol) from which the data has to be migrated to SQL Server 7.0 Using DTS.Could u please help me in finding out all the repeating rows so that my people could go through it manually and make it unique rows. I expect a query that can be executed to select the repeating rows in a staging/temp table that contain all the rows from legacy system. If u could sujest any other alternative u r most welcome...
Thanks in advance..
View 2 Replies
View Related
Sep 3, 2007
Can anyone just point me in the right direction. All I want to do is add some T-SQL to an existing stored procedure to return the number of rows selected into a return value.Does anyone know how to do this?
View 4 Replies
View Related
Jan 26, 2006
I am writing some functions that work on a time series database of prices, ie volatility, correlation. I need to use the SELECT TOP syntax, but cannot do this with a variable, ie 'SELECT @x TOP * from prices'. My solution is to simply have a function for each potential period that will be looked at - 30day_volatility, 60day_volatility, etc. I looked at setting the ROWCOUNT variable but this is not allowed in functions. I haven't posted any DDL because I think the question is general enough - How do I return n ordered rows from a function without using SELECT TOP, or is there a way to use SELECT TOP with a variable that I am not aware of.
Thanks!
View 2 Replies
View Related
Jun 8, 2015
I am using SQL 2014 RTM (may be it's time to upgrade).
I have the following view:
create view [dbo].[SiriusV_Max4SaleList]
as
select m.id as Max4SaleId,
mt.[Description] as [TypeDescription],
CAST(m.[type] as tinyint) as [Type],
m.start_time as [StartTime],
m.end_time as [EndTime],
[Code] ....
I am thinking I may want to remove CAST for department, category, item later on as I don't really care if these columns would be defined as key for my EF model, but I do want to search by these columns. Anyway, this is my current view.
I executed the following select statement once
select * FROM dbo.siriusv_max4saleList where department like 's%' or category like 's%' or item like 's%'
And I believe I got 29 rows initially. However, when I execute this statement now I'm getting just 13 rows. If I execute just the department like 's%' I am getting 0 rows although I can see in the first result a row where department has s in in.
I guess I keep it here since I've created the message already but now I figured out why I am not getting the expected result. I used the condition like 's%' and not like '%s%' which application is doing.
View 4 Replies
View Related
Jan 28, 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. I have to bind the result to a GridView.Please provide separate solutions for SqlServer2000/2005.
I have three tables namely – Forums,Topics and Threads 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 ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads
FROM
Forums f FULL JOIN Topics t ON f.forumid=t.forumid
FULL JOIN Threads th ON t.topicid=th.topicid
GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate
ORDER BY t.topicid ASC,th.lastpostdate DESC
Whose result set is as below:-
forumid
topicid
name
author
lastpostdate
NoOfThreads
1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2
1
1
Java Overall
a@b.com
2008-01-27 14:44:29.000
2
1
2
JSP
NULL
NULL
0
1
3
EJB
NULL
NULL
0
1
4
Swings
p@q.com
2008-01-27 15:12:51.000
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
On modifying the query to:-
SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads
FROM
Forums f FULL JOIN Topics t ON f.forumid=t.forumid
FULL JOIN Threads th ON t.topicid=th.topicid
GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate
HAVING th.lastpostdate=(select max(lastpostdate)from threads where topicid=t.topicid)
ORDER BY t.topicid ASC,th.lastpostdate DESC
I get the result set as below:-
forumid
topicid
name
author
lastpostdate
NoOfThreads
1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2
1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1
I want the result set as follows:-
forumid
topicid
name
author
lastpostdate
NoOfThreads
1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2
1
2
JSP
NULL
NULL
0
1
3
EJB
NULL
NULL
0
1
4
Swings
p@q.com
2008-01-27 15:12:51.000
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 thread) as shown above.
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__79A81403]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__79A81403
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__7C8480AE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__7C8480AE
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].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Threads]
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
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].[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].[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
ALTER TABLE [dbo].[Forums] ADD
PRIMARY KEY CLUSTERED
(
[forumid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Threads] ADD
PRIMARY KEY CLUSTERED
(
[threadid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Topics] ADD
PRIMARY KEY CLUSTERED
(
[topicid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Threads] ADD
FOREIGN KEY
(
[topicid]
) REFERENCES [dbo].[Topics] (
[topicid]
)
GO
ALTER TABLE [dbo].[Topics] ADD
FOREIGN KEY
(
[forumid]
) REFERENCES [dbo].[Forums] (
[forumid]
)
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');
View 7 Replies
View Related
Nov 8, 2007
Hi All,
I am using sql server 2005. I stuck out in a strange problem.
I am using view in my stored procedure, when I run the stored procedure some of the rows get skipped out means if select query have to return 10 rows then it is returning 5 rows or any other but not all, also the records displyaing is randomly coming, some time it is displaying reords 12345 next time 5678, other time 2468.
But if I run seperately the querys written in SP then it returns all the rows. Please give me solution why it is happening like this.
There are indexes in the tables.
Once I shrink the database and rebuild the indexes, from then this problem is happening. I have rebuild the indexes several time, also updated the statistics but nothing improving.
But nothing is improving
View 7 Replies
View Related
Oct 16, 2006
Hello,
I would like return in a file the duplicates lines from a table.
Is it possible with a composant?
Or must I use a sql request.
But my problem with the request is the null value in the cell of the line. When U do a join and there is a null value in the row, there no resoult from the request.
How can U do to solve this problem?
Thank you
Regards.
Laurent Albiac
View 3 Replies
View Related
Feb 27, 2007
Hello experts,I'm trying the run the following query with specific intentions.I would like the query to return 5 results; i.e., 4 distinct and oneduplicate. I am only getting, however, 4 distinct records. I wouldlike the results from the '007' id to spit out twice.I'm not using 'distinct,' and I've tried 'all.' I realize that Icould put my 5 employee id's in a table and do a left or right join; Iwould like to avoid that, however. Any thoughts?SelectEmployee_last_name,Employee_first_name
Quote:
View 3 Replies
View Related
Jul 20, 2005
I know this table is designed wrong for what I am doing but I hope Ican do it. I have a table like this.Prod_A_Jan, Prod_A_Feb, Prod_B_Jan, Prod_B_FebI want a query that returns data like this (two rows of data)"ProdA", Prod_A_Jan, Prod_A_Feb"ProdB", Prod_B_Jan, Prod_B_FebI know two queries can get it but I want one. Any Help would begreat!!!Sheila T.
View 3 Replies
View Related
Jun 25, 2001
I used the following select statement to get duplicate records on Case_number column
select cases.distinct case_link, cases.case_number
from cases
group by case_link
having case_number > 1
I got the error message that
"'cases.warrant_number' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
AND
cases.case_number' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
Any idea on a better statement to use. THANKS FOR YOUR HELP!
View 3 Replies
View Related
Jun 29, 2001
Hi,
I have a table and this is what i did to get the desired result
Select A.col1,count(A.col1)
from Tab1
group by col1
having count(A.Col1) > 1
i tried this - but it didnot worked - it returned col1 as blanks -
Select A.col1,B.Col2,count(A.col1)
from Tab1 A, Tab2 B
where A.col1 = B.col1
group by A.col1 , b.col2
having count(A.Col1) > 1
As I was looking for all the rows that are apperaing more than once.
Now - The problem -
I have to join this table to another table Tab2 to get the other details.
My Tab2 is a table from where I have to pull the Customer DEtails like name,address etc.
How should I write this query?
Any thinuhts?
TIA
View 1 Replies
View Related
Jul 20, 2006
Hi,
i wanna know, how can i check if i have duplicate rows in my table?
thanks
View 12 Replies
View Related
May 16, 2007
Hi. I'm a SQL Server newbie, very experienced with Access, developing an ASP.NET database editor web app. I query the database with a statement more or less in the following form:
SELECT organisation.OrgID, organisation.Name, organisation.whatever FROM services INNER JOIN servicegrouping ON services.serviceID=servicegrouping.serviceID INNER JOIN organisations ON servicegrouping.OrgID = organisations.OrgID WHERE services.service=x OR services.service=y
In other words, I have a database of organisations. The services offered by the organisations are in a separate table, and I only want to return organisations that offer services X or Y.
Okay, now if I did this in Access, this query would return just one record for each organisation that meets the condition, unless I was to include a field from the services table in the SELECT clause, in which case of course I would get one record for each organisation and unique service offered.
But in MS SQL, the query returns duplicate rows if there is more than service offered by the organisation that meets the WHERE condition (=x or =y). Why is this and what do I need to do to my SQL statement to ensure I only get unique rows?
View 2 Replies
View Related
Feb 6, 2008
Hi,
I've a query which gets a set of data from multiple tables -
select *
FROM A
inner JOIN q
ON (RIGHT(q.name,CHARINDEX('-',REVERSE(q.name))-1)= a.id)
inner JOIN t
ON (t.id = q.id)
inner JOIN s
ON (q.name = s.name )
inner join l
on (s.name = l.name
and t.name = l.name)
WHERE A.id = 764
and s.name = '764'
I get repeated # of rows for each id. I've some 136 rows for each q.id ( there are 6 q.ids and hence I get 816 rows instead of 136) These 136 rows are actually divided among thse q.ids as
id=5, 4 rows
id=6, 8 rows
id=7, 24 rows
id=8, 40 rows
id=10, 60 rows
total=136 rows
Let me know what I'm missing here
Thanks for your help!
Subha
View 4 Replies
View Related
Feb 1, 2008
Hello,
I have a question, what does a statement look like that finds the duplicate rows and combines them,
I have a table named PRODUCTS in it 3 columbs Cost, Stock, Part_number.
I need to find all Part_numbers that dublicate, Combine the rows into 1 & combine (sum, add) their stock together is the new row & take an avarerage of their cost and use it as cost in the new row where they combine.
Please help me, I am stalled. Looked all over the internet & could not find anything, I really need this for a project I can not finish.
I have the following SQL statement:
SELECT part_number,
COUNT(part_number) AS NumOccurrences
FROM Products
GROUP BY Part_number
HAVING COUNT(part_number) > 1
View 7 Replies
View Related
May 6, 2008
I have a csv file that I need to import daily into a SQL Server 2005 table. Much of the table contents could just be overwritten with the new csv file, however there are a set of Rows within the table that need to be appended to , rather than overwritten.
There is no Primary Key in the csv file that can be used.
I'm not sure this is the best approach, but what I have been trying to do, is append the entire csv file to the existing table, and then go back and delete the duplicates.
When I run the Delete, it does delete the majority of the records, but leaves a couple hundred behind. The number left behind varies with each run, can't seem to identify a pattern here. Running the Delete a second time does clean up the rows left behind in the first execution of the Delete, and gives the result I want.
Any thoughts as to why this needs to be run twice? Or is a better approach available?
Here is my code -
SELECT [Pkg ID], [Elm (s)], [Type Name (s)], [End Exec Date], [End Exec Time], dupcount=count(*)
INTO temppkgactions
FROM pkgactions
GROUP BY [Pkg ID], [Elm (s)], [Type Name (s)], [End Exec Date], [End Exec Time]HAVING count(*) > 1
DELETE TOP (SELECT COUNT(*) -1 FROM dbo.temppkgactions WHERE dupcount > 1 )
FROM dbo.pkgactions
DROP TABLE temppkgactions
Thanks
View 2 Replies
View Related
Apr 6, 2001
hi,
I want to delete duplicate rows in a table, can any one write a sql for doing that...
please help me in this...
urs
Vj
View 2 Replies
View Related
Aug 9, 2000
Hi,
I have a table with four columns. like id,lastname,
firstname,acctname. I have duplicate values for the three columns other
than id column. like
ID FirstNameLastname Acctname
1 john hopkins jh
2 john hopkins Jh
3 david webb dw
4 david webb dw
5 david webb dw
6 Dan Kennedy DK
I want to eliminate the duplicate rows. id can be any one of them.
Can any one suggest me with a query by which i can do this.
Thanks in advance
Mohan
View 2 Replies
View Related
Jun 25, 2000
I have a table which looks as follow:
field1 field2 field3 field4 field5 ......
A B C A X ......
A B C B Y ......
A B C C Z ......
A B C A Y ......
. . . . . ......
I want to delete all the rows except one row. Anybody can help?
Thank you very much.
View 3 Replies
View Related
Jan 25, 2000
I have a large table that consists of the columns zip, state, city, county. The primary key "zip" has duplicates but the rows are unique.
How do I filter out only the duplicate zips.
Randy Garland
View 2 Replies
View Related
Jan 20, 2000
How do you delete duplicate rows in a table so only one row is left in the table, using T-SQL.
View 1 Replies
View Related
Sep 14, 1999
Hi,
I am encountering a problem. There are lots of duplicate rows in the cobol flat files (due to improper data entry and missing columns values )from where I am transforming data to sql 7. 0 tables using DTS. After transformation , can I some how mark the duplicate rows ? it is not for the purpose of eliminating them, but to enter the missing values and make all the rows complete and unique.
I have the transformed table as a temporary table. Can I add a column like 'status' etc.. and have the column values marked '1' for the repeating rows etc....
Can anyone suggest 'any' possible way of implementing it ?
Thanx
Nisha
View 1 Replies
View Related
Feb 11, 1999
Hai
I have problem in deleting duplicate rows. I have a identity column in my table, if I try to use correlatted sub query with Delete command it gives error.
The other problem I have is I have a date column in my table and update that column with current date and time. If use a query to fetch a records on a particular day , it does not return any rows
select * from rates where ch_date >='02/11/99' and ch_date<='02/11/99'
If I use convert also there is some other problems. Is there any way to force date checkings to be done excluding time.
Thanks
View 6 Replies
View Related
Mar 30, 1999
CAN ANYBODY REPLY FOLLOWING QUESTIONS. I WANT TO DELETE DUPLICATE ROWS
IN MY TABLE WITHOUT USING TRANSACTION TABLE. AND ONE MORE QUESTION HOW
TO GET YESTERDAY DATE BY USING ISQL WINDOW.
THANKS
JK
View 2 Replies
View Related
Jul 9, 1998
Hello,
I have a table (mytable) with the following structure
docs int
field1 varchar(20)
......
the information in the table may look like this
docs field1
1 hello
2 hello
3 test
4 test
5 problem
6 problem
The docs column autoincrements and their is a unique constraint on it. The field1 column does not have any constraints on it.
how does on delete the duplicates without deleting both.
I can write a SQL statement to tell me what docs are dups, and what the field1 values are, but I cannot just delete one
Do I write a cursor? or is there an sql statement that would delete just one?
thanks
Steve Power
View 1 Replies
View Related
Nov 17, 1998
This is an imaginary problem while discussing ROWID in ORACLE.
Consider a table without primary key, unique key, uniuqe index.
A row has inserted into the table many times.
I want to delete all but one dulicated rows. With any 'where' clause all rows(duplicated)
will be deleted. In ORACLE i can achieve this using ROWID as follows:
Delete from Table_name
where < all column values >
and ROWID <> ( Select max(rowid) from Table_name where < all column values > )
How can this be achieved in MS SQL Server 6.5 ?
According to Dr. Codd's Golden rules for RDBMS one is that
One should be able to reach each data value in the database by using
table name, row idenfication value and column name.
Does MS SQL Server 6.5 satisfy this requirement ?
Also How many of Dr. Codd's 13 Golden Rules for RDBMS does MS SQL Server 6.5
Satisfy? Which doesn't ?
Any discussion about Codd's Rules is welcome.
- Gunvant Patil
gunvantp@yahoo.com
View 1 Replies
View Related
Aug 6, 2004
Hi everyone, I'm migrating some information for a client at the moment. They had everything in Excel files and I'm getting them into SQL Server. There are some differences in the way I am storing data and the way they were storing data.
For each client they stored, they had something like
Rel1 Rel2 Rel3
100 101 102
Now, what I have is a seperate row for each of Rel1, Rel2 and Rel3 so I would have 3 seperate rows with identical information except for Rel1. So I would have:
Rel1
100
101
102
So one way I thought of doing it was inserting a new row specifying that the value for Rel2 should be stored in Rel1 and for the next row that the value for Rel3 should be stored in Rel1.
Now, I am able to do this but SQL Server inserts an extra row will the NULL value in Rel1. Does anyone know why this would be happening? I think what it is doing is finding a NULL value in Rel3 after creating the two extra rows and is inserting that NULL. So I think I need to check for NULLs and not allow it to create a new row if, say, Rel3 is NULL.
Any pointers are gladly welcome. (I know it's complicated )
View 1 Replies
View Related
Dec 12, 2007
Hello I am fairly new to SQL and having spent much time over the manual I decided to ask for help. So here's my deal.
I've got a query with 5 tables that I join together
Code:
SELECT * FROM Map
INNER JOIN ThreatCategory
INNER JOIN Threat ON
ThreatCategory.threatCategoryID = Threat.threatCategoryID
INNER JOIN Threat_Map
ON Threat.threatID = Threat_Map.threatID
ON Map.mapID = Threat_Map.mapID
LEFT JOIN person on map.contentPersonID = person.personID
WHERE (((DATEDIFF(dd, Map.dataAcquisitionDate, GETDATE()) > map.goodForDays) and (map.expired = '1'))
or (map.expired = '3'))
The problem is the table Threat_Map is a many to many mapping between the Map table and the Threat table. Eg) A map can have more than one threat and a threat can have more than one map. I know this is not the best way to have a database set up but its out of my hands as to changing the database. What I need help with is this.
My application checks as to whether a certain field in the Map table is expired or out of date (as in the query). If so it gets some required information from the other tables using those joins. However, I don't want to get information for the same Map.mapID that's expired twice. I don't really care which ThreatID I get from the Threat_Map table I just need to get one of them to meet the objects standards. However, so far this seemingly simple task has eluded me. I'd like to do this in SQL. Is there perhaps a way to do this. If not I guess I'll just take care of it in the application.
-Alex
View 1 Replies
View Related
Jan 13, 2005
Suppose that we have the following rows in a MSSQL table :
1administrateur1NULLNULL
2administrateur2NULLNULL
1administrateur1NULLNULL
2administrateur2NULLNULL
How to remove duplicates (leave only the 2 first rows) ?
View 1 Replies
View Related
