SQL Server 2014 :: Eliminate Duplicate Rows When Joining Multiple Tables
Jun 8, 2015
We have the below query which is pulling in Sales and Revenue information. Since the sale is recorded in just one month and the revenue is recorded each month, we need to have the results of this query to only list the Sales amount once, but still have all the other revenue amounts listed for each month. In this example, the sale is record in year 2014 and month 10, but there are revenues in every month as well for the rest of 2014 and the start of 2015 but we only want to the sales amount to appear once on this results set.
SELECT
project.project_number,
project.country_code,
project.project_desc,
gsl.global_service_line_desc,
buy.buyer_desc,
[Code] ....
View 9 Replies
ADVERTISEMENT
Jul 14, 2014
I have tried joining several tables and the result displays duplicate rows of virtually every line/row. I have tried using distinct but this didn't work. I know it could because there's several columns from some of the tables named the same.
select purchaseorders.traderid,
suppliers.name
stockbatches.partid,
allpartmaster.partdesc,
allpartmaster.prodgroup,
[Code]....
View 2 Replies
View Related
Apr 11, 2007
Hello,
I need to eliminate the duplicated rows in sql server 2000, but the duplicate is only for some fields of the row. However, I need all the fields of the row. For example, I have the next structure:
Id_type, number_type, date, diagnosis, sex, age, city
After many analysis I get many rows where the tree first field are repeated, so I need to leave only one but with the all another fields. This is because I need only the first time when the diagnosis appear.
How I can do it??
Thank you very much.
Regards,
Angela
View 7 Replies
View Related
May 30, 2007
Working in Sql Server 2005,got 3 different table.Need to fetch the list of persons.A person can belong to different categories.When i am using inner join
on tables i am getting the duplicate rows because a perosn can belong to different categories.I want that there should be only onoe
row for the person and the different categories he belongs to can come up in single field as comma sepatated string
Now the results are like this:
firstname lastname adress category
abc xyz aaa a
abc xyz aaa b
abc xyz aaa c
I want like below:
abc xyz aaa a,b,c
I am thinking of using cursors in the stored procedure.Can you provide me the solution of this including the stored procedure..
View 7 Replies
View Related
Nov 21, 2007
Dear all,
I built a package to transform data from flatfile into temp table. Then execute a stored procedure to tranform from the temp table into the real table. Since the real table have primary keys so it goes failed when there're duplicate rows from temp table. I let the temp table has no primary key. If I had to check the temp table for duplicate rows it would be using cursor through the data and the package will get slow. Is there task in SSIS to identify the duplicate data and eliminate it without using cursor ?
Thanks in advance,
Best regards,
Hery
View 3 Replies
View Related
Sep 11, 2014
i have a table like below
create table staff_attendance
(
attendance_id int,
attendace_date datetime,
staff_id int,
working_year int,
hours int
)
values like
1 2014-06-30 00:00:00.0ST10121
2 2014-06-30 00:00:00.0ST10122
3 2014-06-30 00:00:00.0ST10122 ----same entry like previous one
4 2014-07-01 00:00:00.0ST10121
5 2014-07-01 00:00:00.0ST10122
6 2014-07-02 00:00:00.0ST10121
7 2014-07-02 00:00:00.0ST10122
8 2014-06-30 00:00:00.0ST10221
9 2014-06-30 00:00:00.0ST10222
10 2014-07-01 00:00:00.0ST1022 1
11 2014-07-01 00:00:00.0ST102 22
12 2014-07-02 00:00:00.0ST102 21
13 2014-07-02 00:00:00.0ST102 22
I Need to find the duplicate rows like same entries which is having more than 1 rows.... how do i find?
View 3 Replies
View Related
Feb 1, 2015
I have 3 Tables
TableA - TAID, Name, LastName
TableB - MaleFriendsName, MaleFriendsLastName [One to many]
TableC - FemaleFriendsName, FemaleFriendsLastName [one to many]
A query returns duplicate results from TableB as well as TableC
TableB and TableC have nothing in common and should not interfere with each other.
with TwoTables as (
select
a.QuickSpec as QuickSpecId,
[Code].....
Resultset returns duplicate values on TableB AND only for 1 record in the results [As per Lynn examples]
View 1 Replies
View Related
Sep 29, 2015
I am trying to join two tables and looks like the data is messed up. I want to split the rows into columns as there is more than one value in the row. But somehow I don't see a pattern in here to split the rows.
This how the data is
Create Table #Sample (Numbers Varchar(MAX))
Insert INTO #Sample Values('1000')
Insert INTO #Sample Values ('1024 AND 1025')
Insert INTO #Sample Values ('109 ,110,111')
Insert INTO #Sample Values ('Old # 1033 replaced with new Invoice # 1544')
Insert INTO #Sample Values ('1355 Cancelled and Invoice 1922 added')
Select * from #Sample
This is what is expected...
Create Table #Result (Numbers Varchar(MAX))
Insert INTO #Result Values('1000')
Insert INTO #Result Values ('1024')
Insert INTO #Result Values ('1025')
Insert INTO #Result Values ('109')
Insert INTO #Result Values ('110')
[Code] ....
How I can implement this ? I believe if there are any numbers I need to split into two columns .
View 2 Replies
View Related
Dec 9, 2014
We have Three Tables in sqlserver2012
Master Table
OrderID PackageID CustomerName
1 1 Abc
2 2 Bcd
3 1 xyz
Child1 Table
OrderID ControlName
1 Row1COlumn1 (It Means Pant in Red Color is selected by user(relation with Child2 Table))
1 Row3Column1 (It Means Gown in Blue Color is selected by user(relation with Child2 Table))
1 Row4Column3 (It Means T Shirt in White Color is selected by user(relation with Child2 Table))
2 Row1Column2 (It Means Tie in Green Color is selected by user(relation with Child2 Table))
2 Row3Column1 (It Means Bow in Red Color is selected by user(relation with Child2 Table))
Child2 Table
PackageID Product Color1 Color2 Color3
1 Pant Red Green Blue
1 Shirt Blue Pink Purple
1 Gown Blue Black Yellow
1 T Shirt Red Green White
2 Tie Red Green White
2 Socks Red Green White
2 Bow Red Green White
We want to have result like
OrderID PackageID CustomerName Pant Gown T Shirt Tie Bow
1 1 ABC Red Blue White x x
Blue
2 2 Bcd x x x Green Red
I have tried
;with mycte as (
select ms.OrderID,ms.PackageID
,ms.CustomerName
, Replace(stuff([ControlName], charindex('Column',ControlName),len(ControlName),''),'Row','') rowNum
,Replace(stuff([ControlName], 1, charindex('Column',ControlName)-1 ,''),'Column','') columnNum
From child1 c inner join MasterTable ms on c.Orderid=ms.orderid)
[code]....
it works if we have a product in one color only. like if we have pant in red and blue then its showing just first record
View 2 Replies
View Related
May 17, 2013
I'm joining several tables and when I add the last one I get duplicate results. How can I get just one for each?
select a.field, b.field, c.field
from atblname as a inner join btblname as b on a.id = b.parent_id
left outer join ctblname as c on a.id = c.parent_id
There are more than one result when joining tbl a and c, but I'm getting a reult for each of them for all results from joining a and b.
View 9 Replies
View Related
Jul 10, 2015
I've been trying to make the following query more performant by breaking it up into smaller pieces.
SELECT MT.A3+MT.A4 AS A34,MT.A3
-- ,M.*
FROM
Master_TAB M
JOIN (SELECT M.A1,t3.A3,t3.A4,M.A6,M.A2,ROW_NUMBER() OVER (PARTITION BY A1,A6,A3,A4 ORDER BY A5 DESC) AS rownum
[Code] ....
I know that the Spill is caused by the Sort but I can't remove the sort (sort can't be done in front end). My master table had 1.7 million rows and almost 200 columns (bad design? I know but can't be changed as there's too much that would be affected) every row is little over 1KB
Here's my attempt...
-- MASTER_TAB has 1.7 million rows and 50 columns
CREATE TABLE [dbo].[tmp_ABC](
[A1] [varchar](13) NOT NULL,
[A2] [varchar](5) NOT NULL,
[A3] [varchar](4) NOT NULL,
[A4] [varchar](4) NOT NULL,
[A5] [int] NULL
) ON [PRIMARY]
[Code] ...
This is the Query that is causing the Spill (in reality I'm supposed to bring back all 200 columns fro the master table but for debug purposes I limited the columns)
Select c.A3+c.A4 as A34, c.A3, c.A1
-- M.*
from tmp_DEF c
join MASTER_TAB M on M.A1 = c.A1 and M.A2 = c.A2
order by c.A3, C.A4
if I just run the following I get no spill:
Select c.A3+c.A4 as A34, c.A3, c.A1
from tmp_DEF c
order by c.A3, C.A4
as soon as I add the Master table as a Join I get the Spill...
I read many articles, tried many suggested things (creating indexes... clustered, non-clustered) without success. Maybe I'm totally in Left Field and should enhance the performance going another route?
View 9 Replies
View Related
Aug 20, 2007
Hi
I want to delete the duplicate rows from two tables and get the resultant non-duplicate rows from both the tables into another table
View 4 Replies
View Related
Jul 19, 2006
My thread titles need work, I know. :o
Ok, lets say I've got:
tblDocuments
id INT PK
documentName VARCHAR
tblUsers
id INT PK
userName VARCHAR
tblDocumentApprovals
userID INT
documentID INT
approvalDate DATETIME
If I want to get a list of documents, and the users who've signed them off (if any), I'd do something like:
SELECT [tblDocuments].[documentName], [tblUsers].[userName ], [tblDocumentApprovals].[approvalDate ]
FROM [tblDocuments]
LEFT JOIN [tblDocumentApprovals] ON [tblDocumentApprovals].[documentID] = [tblDocuments.id]
INNER JOIN [tblUsers] ON [tblUsers].[id] = [tblDocumentApprovals].[userID]
...which is lovely. Except - I don't want a row returned for each user that's signed it off. I want one row for each document, with a field containing a list of the people who've signed it off.
I know that it's bad design. I was reading an article only yesterday on how they're putting this kind of thing into the latest version of Access, and how it's a bit of a kludge. But it'd really, really help me.
How do you do it?
View 8 Replies
View Related
Nov 4, 2015
Is it possible to pivot the output data into multiple rows?
I wanted one row for deleted data and another row for Inserted data, I was looking at UNION ALL and CROSS APPLY but to no avail.
View 9 Replies
View Related
Dec 18, 2014
I am looking for SQL query which uses 2 tables CASH and BALANCE.
eg: Need Tablename, ColumsList and data in the results set.
eg: 10 rows shown below and ordered based on Acct_number
Row1,CASH,ACCT_NUMBER,AMOUNT,DEBIT_CREDIT_FLAG,ENTITY,BUSINESS_DATE,CURRENCY,REFERENCE,TRADE_TYPE,SUB ACC CODE
Row2,BALANCE,ACCT_NUMBER,OPENING_BALANCE,CLOSING_BALANCE,CLOSING_BAL_DEBIT_CREDIT_FLAG,BUSINESS_DATE,CURRENCY
Row3,CASH,10,500,CR,ABC,12/12/2014,USD,INTL,,US05
Row4,CASH,10,1000,DR,DEF,12/12/2014,USD,DOM,,US07
Row5,CASH,10,75,DR,XYZ,12/12/2014,USD,DOM,,US05
Row6,BALANCE,10,500,750,DR,12/12/2014,USD
Row7,CASH,20,500,CR,ABC,12/12/2014,USD,INTL,,US05
Row8,CASH,20,1000,DR,DEF,12/12/2014,USD,DOM,,US07
Row9,CASH,20,75,DR,XYZ,12/12/2014,USD,DOM,,US05
Row10,BALANCE,20,500,750,DR,12/12/2014,USD
View 7 Replies
View Related
Mar 5, 2007
table - employee
empdcno name
1 jon
2 peter
3 john
4 n1
5 n2
6 n3
7 n4
8 n5
9 n6
10 n7
table - personalinfo
empdcno telno address
1 111 aaa
2 222 bbb
3 333 ccc
4 444 ddd
5 555 eee
6 666 fff
7 777 ggg
8 888 hhh
9 999 iii
10 000 jjj
table - hrappempeducs
empdcno schoolcode degree
1 A1 BSCS
3 A2 BSIT
5 A3 degree1
9 A3 degree2
10 A2 degree3
table - hrsetschools
schoolcode schname schaddress
A1 Harvard usa
A2 LaSalle philippines
A3 UP india
A4 s1 peru
A5 s2 japan
Result expected:
name telno address degree schname
jon 111 aaa BSCS Harvard
peter 222 bbb null null
john 333 ccc BSIT LaSalle
n1 444 ddd null null
n2 555 eee degree1 UP
n3 666 fff null null
n4 777 ggg null null
n5 888 hhh null null
n6 999 iii degree2 UP
n7 000 jjj degree3 LaSalle
I am not quite familiar with joins.
Thanks
-Ron-
View 4 Replies
View Related
Dec 5, 2005
I have three tables
1st table is Student
StudnetID (pk)
Other fields…
2nd table is PhoneType
PhoneTypeID (pk)
PhoneType
3rd table is StudentHasPhone
SHPID (pk)
StudnetID (fk)
PhoneTypeID (fk)
PhoneNumber
PhoneType is an auxiliary table that has 5 records in it Home phone, Cell phone, Work phone, Pager, and Fax. Is there a way to do a join or maybe make a view of a view that would allow me to ultimately end up with…
StudnetID: 1
Name: John
HomePhone: 123-456-7890
WorkPhone: 123-456-7890
CellPhone:
Pager: 123-456-7890
Fax:
Memo: This is one student record.
Some students will have no phone number, some will have all 5 most will have one or two. If possible I would like to do a setup like this in my database to keep from having to have null fields for 4 phone numbers that the majority of records won’t have.
Thanks in advanced,
Nathan Rover
View 3 Replies
View Related
Dec 14, 2004
Hi,
This seems like a basic problem but I can't figure out how to resolve it.
I have a query :
SELECT PR.WBS2, SUM(LedgerAR.Amount * - 1) AS Expr5, LB.AmtBud AS budget
FROM PR LEFT OUTER JOIN
LedgerAR ON PR.WBS1 = LedgerAR.WBS1 AND PR.WBS2 = LedgerAR.WBS2 AND LedgerAR.WBS3 = PR.WBS3 LEFT OUTER JOIN
LB ON LB.WBS1 = PR.WBS1 AND LB.WBS2 = PR.WBS2 AND PR.WBS3 = LB.WBS3
WHERE (PR.WBS2 <> '9001') AND (PR.WBS2 <> 'zzz') AND (PR.WBS2 <> '98') AND (PR.WBS3 <> 'zzz') AND (PR.WBS2 <> '') AND (PR.WBS1 = '001-298')
GROUP BY PR.WBS2, LB.AmtBud
ORDER BY PR.WBS2
The output of the above query:
WBS2Expr5budget
0141
0141953000
0143
121724540
1217500
1217622.5800
12171000
12172000
12174000
12174500
121772908000
121793513500
12173445018000
12176596032000
12173801044000
121838100
121913224.5
1220
1221
122262000
12224000
122312702
I want to sum up the middle column and last column grouping by wbs2. However, when I do SUM(lb.amtbud) the budget column is not summing correctly it is summing the column as if the data appeared like this:
0141
01410101410101410103000
01410101410101410147.53000
01410101410101410147.53000
0143
014305
1217
12170101217010121701008000
12170101217010121701008000
12170101217010121701008000
12170101217010121701008000
1217010121701012170101017.58000
121701012170101217010382.58000
12170101217010121701027.58000
121701012170101217010302.58000
12170101217010121701027.58000
121701012170101217010382.58000
121701012170101217010302.58000
1217010121701012170104958000
1217010121701012170102008000
1217010121701012170101017.58000
1217010121701012170101182.58000
1217010121701012170101952.58000
1217060
1217061
121708012170804000
So as a result I am getting 9000 where wbs2 = '0141'
I figure that in my top query I am not joining something correctly. Could someone point out what I am doing wrong?
Thank You.
:)
View 2 Replies
View Related
Dec 9, 2014
We have Three Tables in sqlserver2012
Master Table
OrderID PackageID CustomerName
1 1 Abc
2 2 Bcd
3 1 xyz
Child1 Table
OrderID ControlName
1 Row1COlumn1 (It Means Pant in Red Color is selected by user(relation with Child2 Table))
1 Row3Column1 (It Means Gown in Blue Color is selected by user(relation with Child2 Table))
1 Row4Column3 (It Means T Shirt in White Color is selected by user(relation with Child2 Table))
2 Row1Column2 (It Means Tie in Green Color is selected by user(relation with Child2 Table))
2 Row3Column1 (It Means Bow in Red Color is selected by user(relation with Child2 Table))
Child2 Table
PackageID Product Color1 Color2 Color3
1 Pant Red Green Blue
1 Shirt Blue Pink Purple
1 Gown Blue Black Yellow
1 T Shirt Red Green White
2 Tie Red Green White
2 Socks Red Green White
2 Bow Red Green White
We want to have result like
OrderID PackageID CustomerName Pant Gown T Shirt Tie Bow
1 1 ABC Red Blue White x x
Blue
2 2 Bcd x x x Green Red
I have tried
;with mycte as (
select ms.OrderID,ms.PackageID
,ms.CustomerName
, Replace(stuff([ControlName], charindex('Column',ControlName),len(ControlName),' '),'Row','') rowNum
,Replace(stuff([ControlName], 1, charindex('Column',ControlName)-1 ,''),'Column','') columnNum
[Code] .....
it works if we have a product in one color only. like if we have pant in red and blue then its showing just first record
View 5 Replies
View Related
Oct 14, 2013
I have two tables a and b, where I want to add columns from b to a with a criteria. The columns will be added by month criteria. There is a column in b table called stat_month which ranges from 1 (Jan) to 12 (Dec). I want to keep all the records in a, and join columns from b for each month. I do not want to loose any row from a if there is no data for that row in b.
Here is table a:
CREATE TABLE #A(
naics INT,
ust_code INT,
port INT,
all_qty_1_yr FLOAT,
[Code] ....
And here is table b:
CREATE TABLE #B(
naics INT,
ust_code INT,
port INT,
stat_month INT,
Cum_qty_1_mo FLOAT,
[Code] ....
I do not know how to have the multiple joins for 12 different months and what join I have to use. I used left join but still I am loosing not all but few rows in a, I would also like to know how in one script I can columns separately from stat_mont =’01’ to stat_month =’12’
/****** Script for SelectTopNRows command from SSMS ******/
SELECT a.[naics]
,a.[ust_code]
,a.[port]
,a.[all_qty_1_yr]
,a.[all_qty_2_yr]
[Code] ....
output should have all columns from a and join columns from b when the months = '01' (for Jan) , '02' (for FEB), ...'12' (for Dec): Output table should be something like
* columns from a AND JAN_Cum_qty_1_mo JAN_Cum_qty_2_mo JAN_Cum_all_val_mo JAN_Cum_air_val_mo JAN_Cum_air_wgt_mo JAN_Cum_ves_val_mo FEB_Cum_qty_1_mo FEB_Cum_qty_2_mo FEB_Cum_all_val_mo FEB_Cum_air_val_mo FEB_Cum_air_wgt_mo FEB_Cum_ves_val_mo .....DEC_Cum_qty_1_mo DEC_Cum_qty_2_mo DEC_Cum_all_val_mo DEC_Cum_air_val_mo DEC_Cum_air_wgt_mo DEC_Cum_ves_val_mo (FROM TABLE b)
View 1 Replies
View Related
Jun 9, 2014
I have a straight-forward select query to show work orders for a particular customer as below. I want to add a field value from another table, deltickitem diwhich contains contract records. I need to include the field di.weekchg to show the weekly hire rate, but the joined query must ensure that the both the contract number matches that in the original select and that the item number matches that in the actual select. Additionally, there is the problem that the item can appear more than once in the deltickitem table against a particular contract (if item has been off-hired and then re-hired on the same contract number) - in this case the query must select the record with the highest di.counter number, which I haven't worked out how to put in my query.
This is my basic code, but I keep ending up with duplicate work order lines in my result set.
Select wh.worknumber, wh.custnum, wh.contract, wh.sitename, wh.itemcode, wh.regnum, m.name, di.weekchg,
wh.date_created, wh.task_descr, wh.actual_labour_sale+wh.actual_parts_sale as [Repair Cost]
From worksorderhdr wh Left Join
inventory iv On iv.item = wh.itemcode inner Join
models m On m.id = iv.model_id left join deltickitem di on di.dticket = wh.contract
where wh.custnum = 'BARRATNE' and wh.rejected <> 1 and wh.charge_to_cust = 1
order by wh.date_created
View 9 Replies
View Related
Mar 19, 2008
Hi,
I have a tbl called BANK.
This shows a banking history of transactions and includes a field called TransType and a field called PaymentID.
I also have two other tables called Suppliers and SubContractors.
For each record in the bank, I need to match up a record in either the suppliers or subcontractors tbl based on the PaymentID value. I know if the record relates to either a Supplier or Subcontractor based on the value of the TransType field which will be either SUPPLIER or SUBCONTRACTOR or Null (in which case a match doesn't matter)
I have a working query based on joining just the Supplier tbl.. but how do I do the join to the other tbl aswell?
So overall, for each record in the bank, if the transtype is SUPPLIER I need to look in the supplier tbl for a match for that paymentID, and if the transtype is SUBCONTRACTOR, I need to do the same but in SUBCONTRACTOR tbl.
How would I best write that?
thanks for any help!
View 5 Replies
View Related
Apr 22, 2015
I have got 4 MS Access Database Files, which have got 3 Tables each, means Total 12 Tables which gets updated with new data every evening, by an external application. Means new data gets appended to all these 12 Tables.
I want to have exact same 4 Databases, which have got 3 Tables each, means Total 12 Tables, but WITHIN MS SQL SERVER. And then update all of these 12 Tables every evening, with the corresponding updates from the respective tables from the MS Access Databases.
I do not want to Manually Update all these 12 tables every evening into SQL Server. Hopefully there would be some easier method to do this in automatic manner.
View 4 Replies
View Related
Jul 29, 2003
Table A
JobNoClaimShipType
A1100I
A1200II
Table B
JobNoCost
A150
A1100
Result Expected
JobNOCost Claim Shiptype
A150 100I
A1100 200II
Hi all,
i've given a table structure with data
and the expected result .
I want to establish it in SQL server (7.0)
If i establish the inner join i get 4 rows (2*2)
Please let me know how to get the result
Thanx in adv
Tarriq
View 1 Replies
View Related
Jun 4, 2008
I would like to know if it's possible to return a single record by joining the tables below. [Persons]
PersonID [int] | PageViewed [int]
=============== =================
1 10
2 5
3 2
4 12
[PersonNames] - PersonID JOINS Persons.PersonID
PersonID [int] | NameID [int] | PersonName [nvarchar] | PopularVotes [int]
=============== ============== ======================= ===================
1 1 Samantha Brown 5
1 2 Samantha Green 10
2 3 Richard T 10
3 4 Riko T 0
4 5 Sammie H 0
[AltNames] - backup for searches caused by common spelling mistakes
AltNameID [int] | AltNames [nvarchar]
================ =============================
1 Sam, Samantha, Sammie, Sammy
2 Riko, Rico
[PersonAllNames] - JOINS [PersonNames.NameID] ON [AltNames.AltNameID]
NameID [int] | AltNameID [int]
============= ================
1 1
4 1
3 2
This is ideally what I'd like to have returned: PersonID | PageViewed | MostPopularName | NameSearch
========= ============ ================= =================
1 10 Samantha Green Samantha Brown, Samantha Green, Sam, Samantha, Sammie, Sammy
2 5 Richard T Richard T
3 2 Riko T Riko T, Riko, Rico
4 12 Sammie H Sammie H, Sam, Samantha, Sammie, Sammy
[MostPopularName] is [PersonNames.PopularVotes DESC].[NameSearch] combines all records from [PersonNames.PersonName] and [AltNames.AltNames].
The purpose for this is that I'd like to cache the results table so that all searches can just perform a lookup against the NameSearch field.
Any help would be greatly appreciated.
Thanks, Pete.
View 4 Replies
View Related
Jun 22, 2007
Hey Again,
I've been making great progress but I've hit another road block which a newbie intern like myself can't surpass. What's worse is the fact that no one is in the office today! Maybe someone can point me in the right direction with this SQL:
SELECT
r.[requestID]
,r.[requserID]
,r.[departmentID]
,CONVERT(CHAR(8),r.[submitDate],10)AS submitDate
,CONVERT(CHAR(8),r.[dueDate],10)AS dueDate
,CONVERT(CHAR(8),r.[revisedDueDate],10) AS revisedDueDate
,r.[reqStatus]
,r.[completedDate]
,d.[departmentName]
,s.[statusName]
,u.lastName + ', ' + u.firstName AS submittedBy
,ra.userID
FROMtblUserDepartment ud
INNER JOIN tblRequest rON ud.departmentID = r.departmentID
INNER JOIN tblDepartment dON r.departmentID = d.departmentID
INNER JOIN tblStatus sON r.reqStatus = s.statusID
INNER JOIN tblUser uON r.requserID = u.userID
LEFT JOIN tblRequestAssignee ra ON r.requestID = ra.requestID
WHEREud.userID= @userID
This works great except for one thing. In tblRequestAssignee, you have 1 primary assignee and can have several other assignees (that are not primary). This is denoted by a bit field "isPrimaryAssignee" in tblRequestAssignee. When I run the query, I see every request I want to but it duplicates requests with more than one assignee. What I'm trying to do is make only the primaryAssignee display if there is one. If there's not, then null is displayed (which is already happening).
Like I said, the query is mostly working right except for this duplicate record that displays when there's 2 assignees. Any help would once again be greatly appreciated.
View 1 Replies
View Related
Sep 20, 2006
I have a table with 68 columns. If all the columns hold the same value except for one which is a datetime column I want to delete all but one of the duplicate rows. Preferably the latest one but that is not important. Can someone show me how to accomplish this?
View 5 Replies
View Related
Jul 10, 2014
have tried joining several tables and the result displays duplicate rows of virtually every line/row. i have tried using distinct but failed miserably.
select purchaseorders.traderid,
suppliers.name
stockbatches.partid,
allpartmaster.partdesc,
allpartmaster.prodgroup,
[code]....
View 4 Replies
View Related
Apr 3, 2008
Hi Guyz
say i have a table
10011 NULL NULL Classical NULL
10011 NULL NULL Classical NULL
10004 NULL NULL Classical NULL
10004 NULL NULL Classical NULL
10004 NULL NULL Classical NULL
10005 NULL NULL Classical NULL
i want to eliminate the duplicate records and atable should look like
10011 NULL NULL Classical NULL
10004 NULL NULL Classical NULL
10005 NULL NULL Classical NULL
do we have any simple sql to do it or something complex.
thanks in advance !
View 6 Replies
View Related
May 1, 2015
We have rolap based cube .. when we run the report on that it is take more time than expected.I have run the profiler and identified .. same query is duplicated and running multiple times. I have run the query .. it took 10 seconds..how to eliminate duplicate queries .
View 3 Replies
View Related
Jul 23, 2005
Dear All,I have a table with 10 billion records but there are no key on it. I cannotbuild a key on it as it is the data source.However, the data source exits the duplicated rows.I have used the DTS to transform the data into a new table and delete theduplicated rows. As there are 10 billion records, i need to divide it into 3parts and also the process lasts for 6 hours each part.I want to ask is there any other good methods to slove my problem??ThxEsther
View 1 Replies
View Related
Jan 30, 2008
Hello,
I have a question, i loaded 2 files into SQL and the files have some cells that have the same model number.
how can I merge the cells together that have the same model number and (if possible take the avarage of their cell called price)
(and combine their other cell called stock)
and make it into one cell.
Any help would be very very apriciated. Thank you.
i tryed this but it does not work
SELECT Model_number FROM Products
Join Where Model_number='3CM3C1670800B'
I have also Tryed this, IT SHOULD work but I have an error someWhere:
delete from Productsfrom part_number a join
(select part_number, max(part_number) from part_number group by part_number having count(*) > 1) b
on a.part_number = b.part_number and part_number < b.part_number
View 3 Replies
View Related
Feb 28, 2008
In my employee table has the following fields empid, empFname, empLname, email, city
Say it has data like follows:
1, Lucy, Sam, l@some.com, city1
2. Sam, Wite, l@some.com, city2
3. Laura, Mac, l@some.com, city2
4. Stacy, Soo, s@no.com , city1
So in my case I want to show all the column but I want to eliminate multiple email addresses. I tried Distinct but its not workin because here every column is not distinct. So what should I use?
In my case I only want to show empID 1, 3, 4. I want to show all the columns
View 5 Replies
View Related
