Multiple Tables Grouped By Common Field
Jan 4, 2008
Here is my situation. I am building a report that has three different tables each with their own dataset. Example: Opportunities, Leads, Activities. All three of these datasets/tables have a common field - SalesID. I would like the report to show the first SalesID, then all Opportunities for that SalesID in the Opportunities table, followed by all Leads for that SalesID in the Leads table, followed by all Activities for that SalesID in the Activities table, and then rollover to the next SalesID and repeat that for all SalesIDs. Any suggestions on how I could achieve this? Thanks in advance for all help!!!
View 6 Replies
ADVERTISEMENT
Oct 17, 2005
I know there is some kind of rule against the following SQL statement, but I was wondering what to do to get around this problem (some kind of grouping). Sorry for the stupid question.
SELECT * FROM Table1, Table2 WHERE Table1.ID IS NOT NULL AND Table2.ID IS NOT NULL
Basically I want to select all records from the two tables (they have the same fields, but are just different specialties) and then output them, but there is nothing in common between the two to reference one another, and it ends up in some kind of loop. Thanks. for the help.
View 2 Replies
View Related
Jan 29, 2004
Hello-
I have a sql query that I am using to populate a datagrid. The problem is one of the tables is a month table. and the other tables are full of data. So there is no common column name to match using a inner join "on".
How do i do this?
View 6 Replies
View Related
Dec 1, 2005
I have a database with 10 tables
I have a field named CustID .... I want to find out how many tables have this field CustID
Now one way was check up the design of every table & see whether its there or not (the field)
is there any query through which we can get this info Please help
View 4 Replies
View Related
Feb 10, 2002
This is feeling very hard for me, but is surely very easy for many of you.
I have 2 Tables. "Events" and "Meals". Both have a columns named "EventDate" and "EventTime". I need to be able to compile a list of both and sort by event date and time. For example, a Meal @ 5:30 would place itself between a 4:00 Event, and a 6:30 Event.
PLEASE HELP ME!!!
Pending deadline doom :(
Thanks,
kaskenasy@publishingconcepts.com
View 1 Replies
View Related
Jan 27, 2014
I have a table activity which show the activities of the site. An activity could be related with no other entity or it could be related with an account, or with a contact or with an opportunity. An opportunity and a contact could be also related with an account.
My problem is that I need to show the activity and the related account (if there is any) but because the relationship with an account could be done by different ways, I dont know how to do it without join the account table several times.
This is my current query:
SELECT AC.IdActividad, CU.idcuenta
FROM Actividades AC LEFT JOIN Tareas TA ON TA.IdActividad = C.IdActividad
LEFT JOIN ActividadesXCuenta AXC ON AC.IdActividad = AXC.IdActividad LEFT JOIN dbo.Cuentas CU ON axc.IdCuenta = CU.IdCuenta
LEFT JOIN dbo.ActividadesXOportunidad axo ON AC.IdActividad = axo.IdActividad LEFT JOIN dbo.Oportunidades o ON axo.IdOportunidad = o.IdOportunidad LEFT JOIN dbo.Cuentas CU1 ON o.IdCuenta = CU.IdCuenta
LEFT JOIN dbo.ActividadesXContacto axco ON AC.IdActividad = axco.IdActividad LEFT JOIN dbo.Contactos con ON axco.IdContacto = con.IdContacto LEFT JOIN dbo.Cuentas CU2 ON o.IdCuenta = CU.IdCuenta
As you see, I have the table Cuenta joined 3 times. That means that I need to put the ActivityID field 3 times but based on how the report was developed, I could not do that.
View 3 Replies
View Related
Nov 18, 2007
Hellow Folks.
Here is the Original Data in my single SQL 2005 Table:
Department: Sells:
1 Meat
1 Rice
1 Orange
2 Orange
2 Apple
3 Pears
The Data I would like read separated by Semi-colon:
Department: Sells:
1 Meat;Rice;Orange
2 Orange;Apple
3 Pears
I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..
View 2 Replies
View Related
Apr 12, 2008
Hello all,
Here is an SQL Server 2005 table that lists player scores:
Code Snippet
+--------+--------+----------+
| NAME | POINTS | DATE |
+--------+--------+----------+
| Liz | 7 | 01/04/08 |
| Mark | 20 | 15/03/08 |
| John | 9 | 04/01/08 |
| Liz | 25 | 25/12/07 |
| Liz | 11 | 10/04/08 |
| Mark | 11 | 22/03/08 |
| Patty | 20 | 08/04/08 |
+--------+--------+----------+
I'd like to get, for each player, his/her best performance, including the date. Concretely, my SELECT query should return:
Code Snippet
+--------+--------+----------+
| NAME | POINTS | DATE |
+--------+--------+----------+
| Liz | 25 | 25/12/07 |
| Mark | 20 | 15/03/08 |
| John | 9 | 04/01/08 |
| Patty | 20 | 08/04/08 |
+--------+--------+----------+
does someone have any idea ?
Thx
View 3 Replies
View Related
Sep 21, 2007
Hi,
I have two statements which when I join by a union statement give the folowing:
2005 11 0.000000
2005 12 0.000000
2006 1 0.000000
2006 1 50813.058500
2006 10 0.000000
2006 11 0.000000
2006 12 0.000000
2006 12 63224.511250
2006 2 0.000000
2006 2 59164.234500
2006 3 0.000000
2006 4 0.000000
2006 5 0.000000
2006 6 0.000000
2006 6 82442.570750
2006 7 0.000000
2006 7 61809.497750
2006 8 0.000000
2006 9 0.000000
2007 1 0.000000
2007 2 0.000000
2007 3 0.000000
2007 4 0.000000
2007 5 0.000000
2007 6 0.000000
2007 7 0.000000
2007 8 0.000000
What I want is to merge the values
2006 1 0.000000
2006 1 50813.058500
into one row, the months with zero figures are required.
Thanks
View 1 Replies
View Related
Jul 23, 2005
I have a tables called subsid that I need to change the datatype fromtext to int.I think I got them all but is there a query I can run that will checkall fields call subsid accross all tables that are of type text.
View 1 Replies
View Related
Apr 9, 2007
I want to create a table withmember id(primary key for Students,faculty and staff [Tables])and now i want to create issues[Tables] with foreign key as member idbut in references i could not able to pass on reference as orcondition for students, faculty and staff.Thank You,Chirag
View 3 Replies
View Related
Sep 9, 2015
Conditional Update of a field from multiple tables..I have a target table with two fields: Date and ID..There three source tables: S1, S2, S3, each of them has three fields: Date, ID, and Score...Now I want to update the target table: put the ID into the ID field which has the highest Score from the three tables on each day.
View 13 Replies
View Related
Sep 30, 2015
I have an XML file loaded into a table with XML type column. The XML format is such:
<CsResponse>
<Reports>
<Report>
<Id>186192</Id>
<DateCreated>9/29/2015 1:19:56 PM</DateCreated>
<DateUpdated>9/29/2015 2:19:10 PM</DateUpdated>
<RequestType>Roadway Sign (Damaged/Missing)</RequestType>
[Code] ....
I am trying to create 2 tables, one (Table 1) having:
Id
DateCreated
DateUpdated
RequestType
RequestTypeId
StatusType
StatusTypeIsClosed
The other (Table 2) with:
Id
DateCreated
Key
Value
Ultimately, these 2 table will be joined on Id. Here is the SQL, Table 1 creates properly, and Table 2 creates, but right now 'Id' comes in as NULL. How do I add the Id field from the upper node into Table 2 (excuse my terminology if it is incorrect) ?
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM pubanalysis.dbo.CS_XMLReportData
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
--TABLE 1
INSERT INTO pubanalysis.dbo.CS_RequestReports
[Code] ....
View 5 Replies
View Related
Apr 21, 2008
Hello, I have an aspx page with a text box. The user may enter a large portion of text including symbols like ' or ". On form submitting I would like a database (text or varchar(MAX) ) field to be updated with the value of the text box...The query that I have created for this purpose is the following: "string query ="UPDATE Article SET A_Content='" + content + "' WHERE A_Id=" + id; As I said before the user may (and in most cases must) enter special characters like ' (example: bla bla blah it's very good bla bla blah).The problem is that ' or " close the query string (SQL) and interpreter things that I am reffering to a field's name instead of the fields value (I think) resulting the following error:The identifier that starts with...is too long.Maximum length is 128.Unclosed quotation mark after the character string ...I think that it must be a very common problem with many solutions but for some reason I cant find anything on the web.What is the common practice to overcome the problem?Is there a function that might transform text into SQL acceptable text?Should I create methods for Replacing ' with other characters like ^^^ (text can be quite big...)?Thanx for any suggestions!
View 3 Replies
View Related
Feb 2, 2004
I want to run multiple DTS packages which export data into text files.
There is only one Data Source ..and multiple destinations.
When i write a code for this in VB ,for each Package i need to define the source connectioninividually. Can't i use the same Source connection which i used for the first package in the subsequent packages?
View 1 Replies
View Related
Apr 3, 2006
Information for configuring Service Broker when you have multiple initiators seems to be thin on the ground.
The examples are all point-to-point and seem to require separate logins for each initiator and corresponding exchange of certificates using a multi-pass installation.
E.g. Rushi's ServiceListing API.
We have an application with several hundred workstations which need to send transaction data to department servers and then on to corporate. There is also a need to replicate small amounts of reference data to all levels.
The examples seem to generate an installation and administrative nightmare.
I have a question - would the following scheme work?
1. All our machines are on the same domain so transport security can be handled by Windows Security (One less set of certificates to exchange).
2. Generate 2 Certificates on the Corporate database and export them to the department and workstations
use these pre-installed certificates in each database to handle dialog security for all converations.
Ie. multiple workstations feed into one user id on the
department server using a single certificate. Ditto for all the department servers feeding into corporate.
3. Use Service broker id's to determine where messages are sent (as the same services would be installed at multiple locations).
4. When a database is intalled/restored at a workstation does this invalidate any certificates that are shipped with it?
View 5 Replies
View Related
Aug 26, 2015
I've inherited a table of members that has the following structure:
CREATE TABLE [dbo].[dimMember](
[dimMemberId] [int] IDENTITY(1,1) NOT NULL,
[dimSourceSystemId] [int] NOT NULL CONSTRAINT [DF_dimMember_dimSourceSystemId] DEFAULT ((-1)),
[MemberCode] [nvarchar](50) NOT NULL,
[FirstName] [nvarchar](250) NOT NULL,
[LastName] [nvarchar](250) NOT NULL,
[Code] ....
Based on the way the data loads into the table there's a possibility of some records being near duplicates of each other. For example, we can have a member that has records that have the same first name, last name, SSN, but different addresses, membercodes, subscribercode etc... This can happen in pretty much any variation thereof.
What I want to do, is add a new column and use that to group the similar records under based on comparing on several columns. By this I mean, if a member matches 4 of the 7 values below with another member, we would group these:
First Name (1st 3 characters)
Last Name
DOB
CurrentAddress1
MemberCode
SSN
SubscriberCode
I'm at a loss of how to structure the SQL to update the new column in the table.
View 9 Replies
View Related
Aug 8, 2006
Hi,
Do anyone know or have a suggestion how to get commun columns name between several table in a SQL server database
For example I have table_1(name, age, school) table_2(name, address, city) and table_3(name, department, company)
name is the common column name (I don't care about the data) in this case. Is there a way to do it simple and easy ?
THANK YOU
View 6 Replies
View Related
Jan 28, 2008
Hi,
I'd like to perform a number of different operations on my Common Table expression but I seem to be limited to only one operation. For example I cannot both delete duplicate rows and then perform a select statement. I can only execute one of the statements referencing the common table expression.
What is wrong with my syntax?
;With OrderedTable
AS
(
select Row_number() OVER (partition BY SSNumber order by Department_Id desc ) AS ROWID,* from Employee
)
delete from OrderedTable where RowId != 1
SELECT COUNT(*),SSNumber FROM OrderedTable group by Department_Id order by count(*) desc
View 1 Replies
View Related
Apr 9, 2006
I have two tables that contain product SKUs (12-character strings):
Table 1Product IdSKU...
Table 2ProductVariantIdSKU...
I need to find the MAX (i.e., last used) SKU that exists in either table. I did write two sps, one for each table that I can compare in code and use the larger (latest) one but I am not that facile with JOINS, etc., so I can't figure how how to create a single sp to return the value I am looking for--although I assume this must not only be possible but trivial to more experienced SQLers.
Thanks!
Duncan
View 4 Replies
View Related
Jul 20, 2004
I have 3 jobs each consists of set of stored procedures.The stored procedures have lots of temp tables. And all the jobs run at the same time.
job1:
EXEc sp1
EXEC sp2
EXEC sp3
Job2
EXEC abc1
EXEC abc2
EXEC abc3
EXEC abc4
EXEC abc5
Job3
EXEC xyz1
EXEC xyz2
EXEC xyz3
EXEC xyz4
But the issue is that the stored procedures in the job1 has temp tables with the same name as stored procedures in the job 2 have.
Eg:
procedure abc1 and procedure sp2 have the temp table #temp1.
procedure abc4 and procedure xyz2 have the temp table #temp3.
Like this i have some more common temp tables. So my question is that can I use the temp tables like that.If so does it degrade the perforamnce of the sps.
View 1 Replies
View Related
Apr 15, 2008
hi friends i am new to this forum and to the tech. also , i am reading a csv file and storing the data to the table of sql server 2005 database but the problem is i cannot join both the tables because both the tables doesnot have common cols and ther is no primary key field in any of the coloumn i have tried the sql query but i didn't got the desired output so could anyone help me please see the sql query of mine is like this:
SELECT dbo.table1.country, dbo.table1.code, dbo.table1.rate_min, dbo.table2.start_date_time, dbo.table2.rounded_dur, dbo.table2.cost,
dbo.table2.dialed_digits
FROM dbo.table1 INNER JOIN
dbo.table2 ON dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 2) OR
dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 3) OR
dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 4) OR
dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 5) OR
dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 6)
please help me its urgent
View 3 Replies
View Related
Mar 21, 2006
Hi,
I am trying to query for a common value in a column called "file_auth_nbr" in 30 different tables. I was going to try something like this (see below) but wasn't sure if this was the most efficient, fastest, or correct, way to get what I'm looking for:
Select distinct a.file_auth_nbr from table1 as a
join table2 as b
on a.file_auth_nbr = b.file_auth_nbr
join table3 as c
on a.file_auth_nbr = c.file_auth_nbr
join table4 as d
on a.file_auth_nbr = d.file_auth_nbr
join table5 as e
on a.file_auth_nbr = e.file_auth_nbr
......etc., etc.
Any suggestions would be much appreciated,
Jeff
View 1 Replies
View Related
Oct 22, 2007
Hi i have always used views in my code to group common functionality in my sql expressions and then i can simply call these views in my data access layer by saing:
SqlCommand cmd = new SqlCommand("SELECT * FROM vw_Documents WHERE CategoryID = @CategoryID", cn);
However my view has become so complicated that i had to convert it to a stored procedure called sp_Documents. The problem now though is that is that i wish to do queries against the data returned but i can't simply say:
SqlCommand cmd = new SqlCommand("SELECT * FROM sp_Documents WHERE CategoryID = @CategoryID", cn);
The only way i can see to do it is to create a stored procedure for every single senario i have passing in the appropriate values as parameters. This seems a pretty messy solution to me because i would have repeated logic in all my stored procedures. Therefore i was wondering if there's a simpler way for me to do this or am i just being lazy :).
Appreciate if someone could help,
View 2 Replies
View Related
Feb 20, 2006
Hi guys,
I'm trying to have two common table expression in my stored procedure, but I'm receiving errors when executing it, I found that they can't exist side by side,once I removed 1 of them, the stored procedure executed successfully.
The following are the errors
Code:
Msg 156, Level 15, State 1, Procedure GetProductsByCategory, Line 27
Incorrect syntax near the keyword 'With'.
Msg 319, Level 15, State 1, Procedure GetProductsByCategory, Line 27
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 319, Level 15, State 1, Procedure GetProductsByCategory, Line 33
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
I'm using SQL Server Express
View 4 Replies
View Related
May 17, 2007
Hi!
I'm using Reporting Services Integration Mode and I have a page that has a number of report viewer webparts on it to display reports. Is there a way that I can add another webpart to pass parameters to all the reports at once instead of individually? I am trying to build a dashboard and this is one of the requirements. I see on the Report Viewer web part, that there is an option in Connections to 'Get report parameters from' , but it is greyed out. Any ideas on how to do this?
Thanks!
Brian
View 6 Replies
View Related
Aug 8, 2007
I'm kinda embarrased, it's been quite awhile since I've played with SQL or Access but here's my brain fart. I need to open an access DB by the name of eHomes. Inside there are two tables AD_IMAGE & PROPERTY. Property is the main table and stores most of the info. It has a field in it called AD_ID and others except for the image file name. The AD_IMAGE table just keeps the image file name and has 3 fields: ID - AD_ID - & Image1. The problem is when the ad is deleted from the property table the AD_IMAGE table record referring to the PROPERTY.AD_ID doen't get deleted. So, I just want the AD_IMAGE table cleaned up to match what's active in the PROPERTY table using the common AD_ID fields in both tables.
Thanks
View 1 Replies
View Related
Aug 27, 2014
I'd like to first figure out the count of how many rows are not the Current Edition have the following:
Second I'd like to be able to select the primary key of all the rows involved
Third I'd like to select all the primary keys of just the rows not in the current edition
Not really sure how to describe this without making a dataset
CREATE TABLE [Project].[TestTable1](
[TestTable1_pk] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [int] NOT NULL,
[Edition_fk] [int] NOT NULL,
[Key1_fk] [int] NOT NULL,
[Key2_fk] [int] NOT NULL,
[Code] .....
Group by fails me because I only want the groups where the Edition_fk don't match...
View 4 Replies
View Related
Apr 14, 2008
hi friends i am reading a csv file and storing the data in table i have created the tables like this:
coloumn names datatype constraints
start_date_time varchar(50) allownull = true
ani varchar(50) allownull=true
dialed_digit varchar(50) allownull=true
actual_dur varchar(50) allownull=true
rounded_dur varchar(50) allownull=true
cost varchar(50) allownull=true
and the second table as
coloumn names datatype constraints
country varchar(50) allownull = true
code varchar(50) allownull=true
rate/min varchar(50) allownull=true
now i want to relate and join the table so that i can create a view with the following coloumns such as
start_date_time, dialed_digits, rounded_digits, cost, country, code,rate/min so can any body help me please i have tried outerjoin but it gave me rudundant data so please help me plz its urgent...
View 11 Replies
View Related
Jul 16, 2015
Any tool, script, procedure, or otherwise that will solve this problem?
CREATE TABLE [Table 1] (
Id varchar,
TableTwoId varchar,
OtherData varchar )
CREATE TABLE [Table 2] (
Id varchar,
MoreData varchar )
What is the link between these two tables?
I have databases that have zero keys defined, no foreign key constraints, no unique value constraints. I cannot assume that an Identity column is the Id. The end game is be able to output that [Table 1].[TableTwoId] = [Table 2].[Id] but cannot assume that all linkage in the database will be as simple as saying "if the field name contains a table name + Id then it is the Id in that table."
Currently have written a script that will cycle through all columns and start identifying keys in singular tables based on the distinctness of the values in each column (i.e. Column1 is 99% distinct so is the unique value). It will also combine columns if the linkage is the combination of one or more columns from Table 1 = the same number of columns in Table 2. This takes a long time, and it somewhat unreliable as IDENTITY columns may seem to relate to one another when they don't.
View 7 Replies
View Related
Jul 17, 2015
CREATE TABLE [Table 1] (
Id varchar,
TableTwoId varchar,
OtherData varchar )
CREATE TABLE [Table 2] (
Id varchar,
MoreData varchar )
What is the link between these two tables?
I have databases that have zero keys defined, no foreign key constraints, no unique value constraints. I cannot assume that an Identity column is the Id. The end game is be able to output that [Table 1].[TableTwoId] = [Table 2].[Id] but cannot assume that all linkage in the database will be as simple as saying "if the field name contains a table name + Id then it is the Id in that table."
Currently have written a script that will cycle through all columns and start identifying keys in singular tables based on the distinctness of the values in each column (i.e. Column1 is 99% distinct so is the unique value). It will also combine columns if the linkage is the combination of one or more columns from Table 1 = the same number of columns in Table 2. This takes a long time, and it somewhat unreliable as IDENTITY columns may seem to relate to one another when they don't.
View 2 Replies
View Related
Mar 21, 2008
Hi! I have a general SQL CE v3.5 design question related to table/file layout. I have an system that has multiple tables that fall into categories of data access. The 3 categories of data access are:
1 is for configuration-related data. There is one application that will read/write to the data, and a second application that will read the data on startup.
1 is for high-performance temporal storage of data. The data objects are all the same type, but they are our own custom object and not just simple types.
1 is for logging where the data will be permanent - unless the configured size/recycling settings cause a resize or cleanup. There will be one application writing alot [potentially] of data depending on log settings, and another application searching/reading sections of data.
When working with data and designing the layout, I like to approach things from a data-centric mindset, because this seems to result in a better performing system. That said, I am thinking about using 3 individual SDF files for the above data access scenarios - as opposed to a single SDF with multiple tables. I'm thinking this would provide better performance in SQL CE because the query engine will not have alot of different types of queries going against the same database file. For instance, the temporal storage is basically reading/writing/deleting various amounts of data. And, this is different from the logging, where the log can grow pretty large - definitely bigger than the default 128 MB. So, it seems logical to manage them separately.
I would greatly appreciate any suggestions from the SQL CE experts with regard to my approach. If there are any tips/tricks with respect to different data access scenarios - taking into account performance, type of data access, etc. - I would love to take a look at that.
Thanks in advance for any help/suggestions,
Bob
View 1 Replies
View Related
Jul 23, 2005
I have a table called BidItem which has another table calledBidAddendum related to it by foreign key. I have another table calledBidFolder which is related to both BidItem and BidAddendum, based on acolumn called RefId and one called Type, i.e. type 1 is a relationshipto BidItem and type 2 is a relationship to BidAddendum.Is there any way to specify a foreign key that will allow for thedifferent types indicating which table the relationship should existon? Or do I have to have two separate tables with identical columns(and remove the type column) ?? I would prefer not to have multipleidentical tables.
View 26 Replies
View Related
