T-SQL (SS2K8) :: Get Rows And Sum In Joined Table?

May 2, 2015

I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.

What I tried, but what returns NULL for purchasedamount, where I want purchasedamount to be the sum of the `amount` for THAT item, based on giftregistrypurchases.itemid=giftregistryitems.id:

SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,*
FROM giftregistryitems gi
LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id
WHERE gi.registryid=2

How can I achieve what I need?

Here's my table definition and data:

USE [tt]
GO
/****** Object: Table [dbo].[giftregistry] Script Date: 09-05-15 11:15:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[giftregistry](
[id] [int] IDENTITY(1,1) NOT NULL,

[code]....

Desired results:

purchasedamountidregistryidtitleogimgdescriptionURLamountpricecreatedate

[URL]

View 9 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Update A Field In 3rd Table From 2 Joined Tables

Jul 29, 2015

I have a script that is supposed to run thru 2 joined tables and update a field in the 3rd table. The script works but takes approx. 4 hours to run against 250k records.

UPDATE a
SET Con_Mailings = STUFF((SELECT '; ' + c.ListName
FROM [server].[xxxxx_MSCRM].[dbo].ListBase c with (nowait)
INNER JOIN [server].[xxxxxx_MSCRM].[dbo].[ListMemberBase] b with (nowait)
ON b.ListID = c.ListID
WHERE b.EntityID = a.TmpContactID
FOR XML PATH('')),1,1,'')
FROM [xx_Temp].[dbo].[Lyris_CombinedTest] a

I should end up with something like this in the con_mailings field:

'Mailing1, Mailing2, Mailing3'

View 9 Replies View Related

SQL Server 2012 :: Get Rows And Sum In Joined Table

May 2, 2015

I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.

What I tried, but what returns NULL for purchasedamount:

SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,*
FROM giftregistryitems gi
LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id
WHERE gi.registryid=2

How can I achieve what I need?

Here's my table definition and data:

/****** Object: Table [dbo].[giftregistryitems] Script Date: 02-05-15 22:37:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[giftregistryitems](
[id] [int] IDENTITY(1,1) NOT NULL,

[Code] ....

View 0 Replies View Related

SQL Server 2012 :: Select Rows With Sum Of Column From Joined Table?

May 2, 2015

I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.

What I tried, but what returns NULL for purchasedamount:

SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,*
FROM giftregistryitems gi
LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id
WHERE gi.registryid=2

How can I achieve what I need?

Here are my table definitions and data:

/****** Object: Table [dbo].[giftregistryitems] Script Date: 02-05-15 22:37:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[giftregistryitems](
[id] [int] IDENTITY(1,1) NOT NULL,

[code].....

View 0 Replies View Related

T-SQL (SS2K8) :: Delete And Merge Duplicate Records From Joined Tables?

Oct 21, 2014

Im trying to delete duplicate records from the output of the query below, if they also meet certain conditions ie 'different address type' then I would merge the records. From the following query how do I go about achieving one and/or the other from either the output, or as an extension of the query itself?

SELECT
a1z103acno AccountNumber
, a1z103frnm FirstName
, a1z103lanm LastName
, a1z103ornm OrgName
, a3z103adr1 AddressLine1
, A3z103city City
, A3z103st State

[code]...

View 1 Replies View Related

T-SQL (SS2K8) :: One Table Two Rows

Feb 4, 2015

What I want is to divide the row value for 'OB Dial Attempts' by the row value for '# Ready To Work Inventory', both in the same table.The code below will work, however I think there is a flaw in my logic. The actual table only has one row per category (MatrixCat) and will always have only one row per category.

CREATE TABLE #NumVals (MatrixCat VARCHAR(100), MatrixVal VARCHAR(100));
INSERT INTO #NumVals (MatrixCat, MatrixVal) VALUES
('# Ready To Work Inventory','606'),
('OB Dial Attempts','255');

[code]....

View 8 Replies View Related

T-SQL (SS2K8) :: Compare Rows In The Same Table?

Aug 7, 2014

We have a table setup to track changes that are made to another table, for auditing purposes. How do we compare the most recent record in the change table with the previous record in the change table? Particularly, we have a column named DUE_DATE in the change table and want to identify when the most recent change has a different DUE_DATE than the previous change made.

View 8 Replies View Related

T-SQL (SS2K8) :: Can It Change Columns Of A Table Values To Rows

May 14, 2014

I have a table with this info:

NrCard numberPersonAuto123456789101112
11111111111111111111User1VW Jetta6,46,46,46,45,825,825,825,825,825,825,826,4
22222222222222222222User2Honda CR-V 13,2113,2113,2112,0112,0112,0112,0112,0112,0112,0113,2113,21

How I can get this result:

NrCard numberPersonAutomonthvalue
11111111111111111111User1VW Jetta16,4
11111111111111111111User1VW Jetta26,4
11111111111111111111User1VW Jetta36,4
11111111111111111111User1VW Jetta45,82
11111111111111111111User1VW Jetta55,82
11111111111111111111User1VW Jetta65,82

[code]....

Should I use unpivot or pivot?

View 2 Replies View Related

T-SQL (SS2K8) :: Number Of Rows And Range For Each Partition In A Table?

Mar 13, 2015

Is it possible to show the number of rows and the range for each partition in a table ?

This shows me the range but not the row count per partition

SELECT sprv.value AS [Value], sprv.boundary_id AS [ID] FROM sys.partition_functions AS spf
INNER JOIN sys.partition_range_values sprv
ON sprv.function_id=spf.function_id
WHERE (spf.name=N'myDateRangePF')
ORDER BY [ID] ASC

View 4 Replies View Related

Select Rows Separately From Two Joined Tables

Nov 5, 2014

I have a query which returns all parts and labour lines for a particular work order. It returns all parts lines seperately, but the labour lines are repeated for each row. What I want to accomplish for a given work order, is a list of all the parts lines, followed underneath by a list of all labour lines.This is the code from the report:

select
h.worknumber,
--- Select parts lines and charges
wp.description as [charges desc],
case
when wp.charge_to_cust = 1 then wp.sale_price

[code]...

For this example what I'd like to see is 5 lines here - the labour description and charge under charges description, unit price, qty and est_parts_sale etc, and of course, there could be more than 1 labour line.

View 2 Replies View Related

T-SQL (SS2K8) :: Send Multiple Rows With One HTML Table In Email?

Dec 2, 2014

I have a job below, which takes the results and send to the users in email.But I have a question, how can I send only one email with all rows, not to send the for every row on table separated email.

DECLARE @Body VARCHAR(MAX)
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @RowCountINT
DECLARE@idINT
declare@departmentnvarchar(30)

[code]....

View 2 Replies View Related

T-SQL (SS2K8) :: Check How Many Rows Delete Updated Per Day And Store It In Another Table?

May 8, 2015

how to track how many rows updated or deleted per day in a single table and load the information in another table .

View 7 Replies View Related

T-SQL (SS2K8) :: Delete All Rows Satisfying Certain Condition From Table A And Related Records From B And C

Apr 14, 2015

I have around 3 tables having around 20 to 30gb of data. My table A related to table B by a FK and same way table B related to table C by FK. I would like to delete all rows satisfying certain condition from table A and all corresponding related records from table B and C. I have created a query to delete the grandchild first, followed by child table and finally parent. I have used inner join in my delete query. As you all know, inner join delete operations, are going to be extremely resource Intensive especially on bigger tables.

What is the best approach to delete all these rows? There are many constraints, triggers on these tables. Also, there might be some FK relations to other tables as well.

View 3 Replies View Related

T-SQL (SS2K8) :: Write Into A Table User-entered Value And Increment That Number N Times As Existing Rows

Jun 25, 2014

I need to have a script where it ask the user for a value, the script will search for all records that match the value. Then it will display the numbers of records found and ask the user to enter a different value. The rest of the script will use this new value and increment by 1 n times as the number of records found. I started the script where it will ask for "HANDLE" and display the number of records found with that "HANDLE"

declare @HANDLE as varchar(30)
declare @COUNT as varchar(10)
declare @STARTINV as varchar(20)

set @HANDLE = ?C --This is the parameter to search for records with this value
set @STARTINV = ?C --User will input the starting invoice number
SELECT COUNT as OrderCount FROM SHIPHIST
where HANDLE = @HANDLE

I just can't figure out how to proceed to use the entered invoice # and increment by 1 until it reach the number of records found.

This will be the end results:

Count=5 --results from query
STARTINV=00010 --Value entered by user

Handle,Inv_Num
AAABBB,00010
AAABBB,00011
AAABBB,00012
AAABBB,00013
AAABBB,00014

View 9 Replies View Related

Analysis :: Hierarchy Based On Dimension Table Joined Multiple Times Against A Fact Table?

Aug 11, 2015

I am working on a model where I have a sales fact table. Each fact record has four different customer fields (ship- to, sold-to, payer, and bill-to customer). I have one customer dimension table that joins to the sales fact table four times (once for each of the customer fields above).  When viewing the data in Excel, I would like to have four hierarchies (ship -to, sold-to, payer, and bill-to customer) within Customer. 

Is there a way to build hierarchies within my Customer dimension based on the same Customer table?  What I want is to view the data in Excel and see the Customer dimension.  Within Customer, I want four hierarchies. 

View 2 Replies View Related

Update Temp Table With Stored Procedure Joined With Table

Sep 8, 2006

Hello

Is it possible to insert data into a temp table with data returned from a stored procedure joined with data from another table?

insert #MyTempTable

exec [dbo].[MyStoredProcedure] @Par1, @Par2, @Par3

JOIN dbo.OtherTable...

I'm missing something before the JOIN command. The temp table needs to know which fields need be updated.

I just can't figure it out

Many Thanks!

Worf

View 2 Replies View Related

SQL Server 2014 :: Repetition Of A Table When Joined With Another Table

Jul 29, 2014

table1

id value
1 11
2 12
3 13
4 14

table2

id1 value1
1 21
2 22
1 31
2 32

in need output as follows

id value id1 value1
1 11 1 21
2 12 2 22
3 13 null null
4 14 null null
1 11 1 31
2 12 2 32
3 13 null null
4 14 null null

View 9 Replies View Related

T-SQL (SS2K8) :: Rows Into Columns - Remove Duplicates And Variable Rows

Aug 5, 2014

I managed to transpose rows into columns.

;WITH
ctePreAgg AS
(
select top 500 act_reference "ActivityRef",
row_number() over (partition by act_reference order by act_reference) as rowno,
t3.s_initials "Initials"
from mytablestuff
order by act_reference

[code]...

But what I would love to do next is take each of the above rows - and return the initials either in one column with all the nulls and duplicate values removed, separated by a comma ..

ref, initials
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW

OR the above but using variable number of columns based on the maximum number of different initials for each row.this is not strictly required, but maybe neater for further work on the view

ref, init1,init2
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW

View 6 Replies View Related

Delete From Joined Table

Dec 20, 2006

How do I delete from a joined table....

I want to delete all the records from transaction AND order table where status is > 4

sumfing like:


DELETE ORDERS.*, TRANSACTIONS.*
from ORDERS
INNER JOIN TRANSACTIONS
where ORDERS.order_no = TRANSACTIONS.order_no
and status > 4

but doesnt seem 2 like dat???

THANKS :)

View 5 Replies View Related

When To Use A Joined Table For Comments?

Nov 9, 2005

I have a table with almost a million rows, although it's quite slim with just ID, date, userID, JobID etc.

Now I want to the ability to add comments to some (probably less than 1%) of those lines.

The question is whether to create a separate comments table to join to it, or to create a comments field within the existing table? The comments field would obviously default to NULL, so wouldn't bloat the table unnecessarily if I add that field (right?), and would always be selected with the row from that table, so I'm leaning towards the latter alternative.

Any thoughts, words of warning?

Thanks
Mark

View 17 Replies View Related

Joined Table -- Display In Datagrid

Apr 20, 2007

Ok here goes.  I have 3 tables, one holds case info, the 2nd holds possible outcome on the charges, and they're joined on a 3rd table (CaseOutComes).  With me so far?  Easy stuff, now for the hard part.
Since there's a very common possiblitly that the Case has multiple charges, we need to track those, and therefore, display them on a datagrid or some other control.  I want the user to be able to edit the info and have X number of dropdowns pertaining to how many ever charges are on the case.  I can get the query to return the rows no sweat, but ...merging them into 1 record (1 row) with mutiple drops is seeming impossible -- I thought about using a placeholder and added the controls that way, but it was not in agreement with what I was trying to tell it .
Any ideas on how to attack this?

View 3 Replies View Related

Set-based Update - Table Joined On Itself

Dec 15, 2005

Guys - sorry for the long post - hope it's clear

DDL/DML below

I want to update the startdate column (for all rows) so that when period is 0 then the new value
is a hardcoded value (say '01-Dec-2000') but for all other rows it takes the value in the
enddate column for the row of the previous column (with the same freq)

ie the startdate column for period 1 takes the enddate value for period 0 and so on for a particular freq

create table #periods (period int , startdate datetime , [enddate] datetime , freq int)
insert #periods ( period , startdate , enddate , freq)
select 0 , '01-Jan-1900' , '31-Jan-2001' , 1
union all
select 1 , '01-Jan-1900' , '28-Feb-2001' , 1
union all
select 2 , '01-Jan-1900' , '31-Mar-2001' , 1
union all
select 3 , '01-Jan-1900' , '30-Apr-2001' , 1
union all
select 4 , '01-Jan-1900' , '31-May-2001' , 1
union all
select 0 , '01-Jan-1900' , '31-Jan-2002' , 3
union all
select 1 , '01-Jan-1900' , '28-Feb-2002' , 3
union all
select 2 , '01-Jan-1900' , '31-Mar-2002' , 3
union all
select 3 , '01-Jan-1900' , '30-Apr-2002' , 3
union all
select 4 , '01-Jan-1900' , '31-May-2002' , 3

select * from #periods -- gives

periodstartendfreq
01900-01-01 00:00:00.0002001-01-31 00:00:00.0001
11900-01-01 00:00:00.0002001-02-28 00:00:00.0001
21900-01-01 00:00:00.0002001-03-31 00:00:00.0001
31900-01-01 00:00:00.0002001-04-30 00:00:00.0001
41900-01-01 00:00:00.0002001-05-31 00:00:00.0001
01900-01-01 00:00:00.0002002-01-31 00:00:00.0003
11900-01-01 00:00:00.0002002-02-28 00:00:00.0003
21900-01-01 00:00:00.0002002-03-31 00:00:00.0003
31900-01-01 00:00:00.0002002-04-30 00:00:00.0003
41900-01-01 00:00:00.0002002-05-31 00:00:00.0003



Desired result
select * from #periods -- gives

periodstartendfreq
02000-12-01 00:00:00.0002001-01-31 00:00:00.0001
12001-01-31 00:00:00.0002001-02-28 00:00:00.0001
22001-02-28 00:00:00.0002001-03-31 00:00:00.0001
32001-03-31 00:00:00.0002001-04-30 00:00:00.0001
42001-04-30 00:00:00.0002001-05-31 00:00:00.0001
02000-12-01 00:00:00.0002002-01-31 00:00:00.0003
12002-01-31 00:00:00.0002002-02-28 00:00:00.0003
22002-02-28 00:00:00.0002002-03-31 00:00:00.0003
32002-03-31 00:00:00.0002002-04-30 00:00:00.0003
42002-04-30 00:00:00.0002002-05-31 00:00:00.0003


/*
I know I need a case statement to test for column 0 and to join the table on itself and have put something together
but it fails for column 0 and updates to NULL - I think it must be to do with the join ??

This is what I've got so far :

UPDATE PA1
SET
PA1.Startdate =
CASE
WHEN PA2.period = 0
THEN
2000-12-01 00:00:00.000
ELSE
PA1.Enddate
END
FROM #periods AS PA1
JOIN #periods AS PA2 ON PA1.Freq = PA2.Freq AND PA1.Period = PA2.Period + 1

Any help gratefully received as always
*/

View 5 Replies View Related

Return Values Not In Joined Table

Jul 24, 2012

I'm looking to pull back some results but not include those that have a value in a column that matches a value in a temp table column.

The below code returns all the values rather than excluding those that are in the temp table.

Code:
------Folder Differences-----
--Create Temp Table--
CREATE TABLE #fdiff
(foldername VARCHAR(255))
SELECT
replace(
replace(foldername,'[Template] ','')

[Code] .....

View 2 Replies View Related

Selecting Only The Last Record In Joined Table

Oct 10, 2005

Hello everyone, I have a query problem.I'll put it like this. There is a 'publishers' table, and there is a'titles' table. Publishers publish titles (of course). Now I want to make aquery (in MS SQL Server) that would return the last title published by everyof the publishers. Quite clear situation. But I can't make it work.If I use inner join (which I should, because I need data from both tables)then I get a result showing all publishers and all titles. What I want toget is all publishers, and only their last title, so I don't have more thanone line for the same publisher, and this line should contain publisherdetails and last title details.I tried using DISTINCT, but it works on a whole resultant row rather then acolumn, and since rows are all distnict (because they also contain columnsfrom titles) this didn't help me.What I can do is (in my application) first get a list of publishers, andthen loop through them selecting only the last title belonging to eachpublisher. I want to see if there is a way to accomplish the same thing withan SQL query (or maybe a stored procedure, view, or whatever). Anything ispossible, as long as it stays within SQL server and doesn't rely on theclient application.Of course, both 'publishers' and 'titles' tables have a primary key('publisherID', and 'titleID'), and 'titles' has a 'publisherID' columnwhich relates titles with publishers.Help :)

View 4 Replies View Related

How To Update Joined Table Using Instead Of Triggers

Jun 25, 2007

Hi
i have a view that contain multiple tables from my database and i want to view it on datagridview and update it's data
some people says you can update joined tables using instead of triggers
how is that ?is there any example ?

thanks in advance.

View 4 Replies View Related

How To Get Aggregate Column From Joined Table

Feb 6, 2008

Can someone please help me with a better way to format the following query. This works, but I know it is hidious.






Code Snippet

select

convert(varchar, processed, 101) as Date,
count(o.id) as [# Orders],
sum(distinct a.runnercount) as [# Runners],
sum(o.total) as [$ Gross],
sum(o.fee) as [$ Fees],
(sum(o.total)-sum(o.fee)) as [$ Net]

from [order] o join (select convert(varchar,processed,101) as date, count(*) as runnercount from orderitem oi inner join [order] o on o.id = oi.orderid where typeofextraid = 4 group by convert(varchar,processed,101)) a on convert(varchar,processed,101) = a.date

where statemented = @statemented
group by convert(varchar, processed, 101)
2 tables: Order and OrderItem. I need the sum of a specific record type from the OrderItem table along with all the other aggregate columns group by day.

Thanks.

Nathan

View 1 Replies View Related

T-SQL (SS2K8) :: Get Rows From C If Linked Rows In B Contain All Rows In A

Oct 28, 2014

I have 3 tables...

JobRequirements (A)
JobID int
QualificationTypeID int

EmployeeQualifications (B)
EmployeeID int
QualificationTypeID int

Employee (C)
EmployeeID int
EmployeeName int

I need to return a list of all employees fit for a specific job ... The criteria is that only employees who have all the JobRequirements are returned. So if a job had 3 requirements and the employee had just 2 of those qualifications, they would not be returned. Likewise, the employee might have more qualifications than the job requires, but unless the employee has all the specific qualifications the job requires they are not included. If an employee has all the job qualifications plus they have extra qualifications then they should be returned...

How to only return those records where all the child records are present in the other table..

View 5 Replies View Related

How To Select Multiple Fields From A Joined Table

Mar 11, 2008

I have two tables - products and productpropertyvalue

I need to select multiple fields from the productpropertyvalue as it corresponds to the product id. The script I am using is

select a.id, a.productname, a.siteprice,
b.propertyvalue

from product a, productpropertyvalue b

where a.id = b.productid and propertyid=590

This allows me to extract only 1 propertyid. I need to make it add 3 other columns for propertyid=589, 617, 615

Any help solving this problem would be appreciated - thanx!

Ron

View 14 Replies View Related

SUM Of All Calculated Columms On A Joined Child Table

Apr 9, 2008

Hi,
This is probably straightforward but I'm not sure how to approach this problem.

I have 2 tables, one parent and a child.
The child table has a Quantity and a Price field and also a FK to the parent table.

I want the parent table to display its own row data along with the Total Cost of the child rows in the child table.
So it needs to multiply the price by the quantity but also do a sum of this calculated column.

To start off with I tried SUM( TOTALCOST ) and it didn't like it.
I've got an idea I might need to create a View but was wondering what is the standard way of doing something like this?
E.g.

Parent Table

PK TOTALCOST
Row 1 50
Row 2 5

Child Table

PK FK Quantity Price TOTALCOST
Row 1 1 2 10 20
Row 2 1 3 10 30
Row 3 2 1 5 5

Thanks.

View 13 Replies View Related

Page 2 - How To Select Multiple Fields From A Joined Table

Mar 11, 2008

float

View 1 Replies View Related

UPDATE Into JOINed Table - Access Vs. SQL Server Syntax

Sep 13, 2007

I am trying to get a SQL statement to work with both Access 2000 and SQL Server 2000.

The statement that works in SQL Server is:

---
UPDATE [myTable2]

SET
[myTable2].[FieldA] = 'Hello',
[myTable2].[FieldB] = 2,
[myTable2].[FieldC] = 'xxx',
[myTable2].[FieldD] = 0

FROM [myTable1] INNER JOIN
(myTable2 INNER JOIN [myTable3]
ON [myTable2].[FieldX]=[myTable2].[FieldY])
ON [myTable1].[FieldZ]=[myTable2].[FieldY]

WHERE ([myTable2].[FieldY]=1)
And ([myTable3].[FieldZ]='xxx');
---


(names have been changed to protect the innocent)


The statement that works in Access is:

---
UPDATE [myTable1] INNER JOIN
(myTable2 INNER JOIN [myTable3]
ON [myTable2].[FieldX]=[myTable2].[FieldY])
ON [myTable1].[FieldZ]=[myTable2].[FieldY]

SET
[myTable2].[FieldA] = 'Hello',
[myTable2].[FieldB] = 2,
[myTable2].[FieldC] = 'xxx',
[myTable2].[FieldD] = 0

WHERE ([myTable2].[FieldY]=1)
And ([myTable3].[FieldZ]='xxx');
---


It seems that neither will accept the other format. Can anyone suggest how I can rearrange the statement so that it works in both?

View 2 Replies View Related

SQL Server 2008 :: Table Returning Duplicates When Joined On

Jul 7, 2015

We currently have an application whereby the user will come along sign up add some information about them, choose what interests / hobbies they're in to and then click save.After completing the sigh up they're able to search for other individuals within locations (look at this functionality as a dating site) We currently have a stored procedure whereby when the user clicks Advance Search they fill in all the fields they filled in when signing up and when they click search we return all users that match the data the user has selected. This part is working correctly.

The issue im having is extending this stored procedure to take a user defined table type which has two columns UserId and ActivityId both columns are nullable.This user defined table type is referred to in two places, when the user signs up and when they do an advance search.When the user does an advance search he / she can choose a list of hobbies / interests this will then be passed in to the database and I will join on my User_Activities table to find the users that have the Activities passed in.When I join on this table I get duplicate records purely because one user may have 5 - 10 activities depending on how active the individual is. My current stored procedure looks like this

@Ambition int = null,
@Body int = null,
@Diet int = null,
@Drinking int = null,
@Ethnicity int = null,
@Exercise int = null,
@HeightFrom int = null,

[code]...

So my question is how can I select all users that match the criteria passed in, as well as finding the users that match the activities passed in (the user can either match all of them or a minimum of 1). Also my User_Activities has two columns UserId and ActivityId identical to the user defined table type

View 9 Replies View Related

Transact SQL :: Return Most Recent Entry On Joined Table?

Sep 29, 2015

Empid 1 has 2 entries for the date 09/01/2015 and my left join returns both of those entries.  What do I need to alter to make it so that only the most recent entry is returned not both entries?

Create Table #one
(
empid varchar(100)
,empbadgeid varchar(100)
,emplunchtime decimal(18,4)
,empbreaktime decimal(18,4)
,empworktime decimal(18,4)

[code]....

View 5 Replies View Related







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