January 4, 2016 at 1:14 pm
Hello, So I'm trying to write a query for a timeline view in a web app that I'm developing. I have the following tables and each table has a date field:
Table Date column
--------------------------------------------------
email Email_date
events event_date
notes note date
assets asset_date
Do I need to do a join to accomplish showing these column in a date asc type of view
So for example if a person got an email on 1/20/2015 then a note on 1/25/15 and then an asset on 1/30/15 it would show it like
asset
note
I need to select multiple columns from each table. I would also like to sort by year, then month then day.
Thanks in Advance.
Sartis
January 4, 2016 at 1:21 pm
sartis (1/4/2016)
Hello, So I'm trying to write a query for a timeline view in a web app that I'm developing. I have the following tables and each table has a date field:Table Date column
--------------------------------------------------
email Email_date
events event_date
notes note date
assets asset_date
Do I need to do a join to accomplish showing these column in a date asc type of view
So for example if a person got an email on 1/20/2015 then a note on 1/25/15 and then an asset on 1/30/15 it would show it like
asset
note
I need to select multiple columns from each table. I would also like to sort by year, then month then day.
Thanks in Advance.
Sartis
Hi and welcome to SSC. It is difficult to answer your question as posted because there is not a lot in the way of details here. It seems like you would join those tables and then order by UserName and then the various date columns. If you can provide some consumable ddl and sample data (check out the first link in my signature) we can help you with the specifics.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 4, 2016 at 1:38 pm
I apologize, ok, so i have several tables I want to select data from, one of the columns in each table is a date column. So, I guess my question is, How can I select data from multiple tables and sort the results from the various date columns.
January 4, 2016 at 1:44 pm
sartis (1/4/2016)
I apologize, ok, so i have several tables I want to select data from, one of the columns in each table is a date column. So, I guess my question is, How can I select data from multiple tables and sort the results from the various date columns.
Like I said previously you would need to use an ORDER BY. I can't give you a lot of detail because I have idea what your data looks like or how you are querying this data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 4, 2016 at 1:55 pm
Here is a select from 2 tables in my database:
SELECT TOP 1000 [email_id]
,[email_to]
,[email_cc]
,[email_subject]
,[email_body]
,[email_sh_id]
,[email_date]
,[email_time]
,[email_attachment_name]
,[email_from]
FROM [EngagEnterprise40].[dbo].
SELECT TOP 1000 [note_id]
,[note_stakeholder_id]
,[note_date]
,
,[note_time]
FROM [EngagEnterprise40].[dbo].[stakeholder_notes]
Does that help?
January 4, 2016 at 2:05 pm
sartis (1/4/2016)
Here is a select from 2 tables in my database:SELECT TOP 1000 [email_id]
,[email_to]
,[email_cc]
,[email_subject]
,[email_body]
,[email_sh_id]
,[email_date]
,[email_time]
,[email_attachment_name]
,[email_from]
FROM [EngagEnterprise40].[dbo].
SELECT TOP 1000 [note_id]
,[note_stakeholder_id]
,[note_date]
,
,[note_time]
FROM [EngagEnterprise40].[dbo].[stakeholder_notes]
Does that help?
No that doesn't help. You mentioned something about a person getting an email and then a note. What relates these two tables together? What about the other two or three tables? Simply posting a select statement demonstrates you did NOT read the link I suggested earlier. Here is a link with a more detailed write up. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/%5B/url%5D
The biggest challenge here is that I have no idea what your table structure is like (think datatypes in addition the the column names). I don't have any sample data to work with. I have no idea what you are trying to do other than a pretty vague forum post. I am trying to help but unless you post some details I can throw up a query that might work but might be miles away from what you need too.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 4, 2016 at 2:34 pm
DROP TABLE stakeholder_notes;
CREATE TABLE stakeholder_notes(
note_id INTEGER NOT NULL PRIMARY KEY
,note_stakeholder_id NVARCHAR(50) NOT NULL
,note_date DATETIME NOT NULL
,note VARCHAR(11) NOT NULL
);
INSERT INTO stakeholder_notes(note_id,note_stakeholder_id,note_date,note) VALUES (36,N'3285','2010-07-15 00:00:00.000','Sample Note');
INSERT INTO stakeholder_notes(note_id,note_stakeholder_id,note_date,note) VALUES (37,N'3771','2010-07-15 00:00:00.000','sample note');
INSERT INTO stakeholder_notes(note_id,note_stakeholder_id,note_date,note) VALUES (38,N'2738','2010-07-21 00:00:00.000','sample note');
DROP TABLE sh_assets;
CREATE TABLE sh_assets(
sh_asset_id INTEGER NOT NULL PRIMARY KEY
,sh_asset_name VARCHAR(500) NOT NULL
,sh_id VARCHAR(50) NOT NULL
,sh_asset_date DATETIME NOT NULL
);
INSERT INTO sh_assets(sh_asset_id,sh_asset_name,sh_id,sh_asset_date) VALUES (6,'12-1814 Final.docx','6247','2013-09-06 00:00:00.000');
INSERT INTO sh_assets(sh_asset_id,sh_asset_name,sh_id,sh_asset_date) VALUES (7,'COTNS FL10.pdf','6253','2013-09-06 00:00:00.000');
INSERT INTO sh_assets(sh_asset_id,sh_asset_name,sh_id,sh_asset_date) VALUES (8,'aa_ww_quote.pdf','5004','1900-01-01 00:00:00.000');
INSERT INTO sh_assets(sh_asset_id,sh_asset_name,sh_id,sh_asset_date) VALUES (9,'AD_certificate.pdf','5004','1900-01-01 00:00:00.000');
INSERT INTO sh_assets(sh_asset_id,sh_asset_name,sh_id,sh_asset_date) VALUES (11,'ComEd Logo.bmp','5003','1900-01-01 00:00:00.000');
INSERT INTO sh_assets(sh_asset_id,sh_asset_name,sh_id,sh_asset_date) VALUES (12,'MicroBook Read Me.pdf','7255','2013-12-03 00:00:00.000');
INSERT INTO sh_assets(sh_asset_id,sh_asset_name,sh_id,sh_asset_date) VALUES (13,'3828EL3010F-1.pdf','5003','1900-01-01 00:00:00.000');
INSERT INTO sh_assets(sh_asset_id,sh_asset_name,sh_id,sh_asset_date) VALUES (15,'bob.txt','7259','2014-03-11 00:00:00.000');
INSERT INTO sh_assets(sh_asset_id,sh_asset_name,sh_id,sh_asset_date) VALUES (16,'badlands.jpg','7260','1900-01-01 00:00:00.000');
I'm just doing two tables for simplicity sake.
I've tried this query:
SELECT note_id
,note_stakeholder_id
,note_date
,note
,sh_asset_id
,sh_asset_name
,sh_id
,sh_asset_date
FROM stakeholder_notes,sh_assets
order by note_date,sh_asset_date asc
January 4, 2016 at 2:42 pm
This is much better. Thanks.
Now we have the next challenge which I asked previously also. Is there something that states which row(s) from sh_asset belong to a given stakeholder? Or do each of these get sent to every stakeholder?
From your example data what do you expect for output?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 4, 2016 at 2:51 pm
Hi,reading between the lines, i expect you are eventually going to need to combine a select statement from each of the tables. I have created a mock up of what your final query might look like, hopefully it will be of some use in terms of a general structure
--initiate your CTE here.......
with COMBINEDSELECTION as(
--select the fields (that will need compatible data types from each of your tables)
--select statement will need identical number of fields selected
-- ( 2 fields from your table to get you started below)
select [email_id] as itemid,[email_date] as itemdate
from [EngagEnterprise40].[dbo].
union all
select [note_id] as itemid,[note_date] as itemdate
from [EngagEnterprise40].[dbo].[stakeholder_notes]
--continue with the pattern above for more tables
--remember to follow the rules of comaptible data types and number of fields selected
)
-- you can then query the results of your combined select statements.
-- use any fields you have included in your select statement above
SELECT itemid, itemdate
FROM COMBINEDSELECTION
order by itemdate
January 4, 2016 at 3:11 pm
If a person gets an email and a note, how do you which person the rows in the table is for? If a person also has assets, how do you tell who the assets belong to? I would think you would need a foreign key to a persons table for this.
If you want to query the different tables, you're going to need them joined together somehow. This is usually done be a foreign key. If you want to query the things (emails, notes, assets, etc.) for a particular person, you're going to need to apply a filter to show only that one person. This is usually defined in the design somewhere, but I don't see where.
The bottom line is that we can't see what you see, so we can't know what suggestions to offer. Do you have a list of your requirements?
January 4, 2016 at 3:48 pm
Sean Lange (1/4/2016)
This is much better. Thanks.Now we have the next challenge which I asked previously also. Is there something that states which row(s) from sh_asset belong to a given stakeholder? Or do each of these get sent to every stakeholder?
From your example data what do you expect for output?
sh_id is the id of the stakeholder, it links to a contactrecord table which I have not posted here
note_stakeholder_id links to the contactrecord table as well.
the assets are just files that are associated with the stakeholder, a stakeholder can have many assets associated with them
Notes are just notes associated with the stakeholder, a stakeholder can have many notes associated with them.
January 5, 2016 at 5:13 am
sartis (1/4/2016)
Sean Lange (1/4/2016)
This is much better. Thanks.Now we have the next challenge which I asked previously also. Is there something that states which row(s) from sh_asset belong to a given stakeholder? Or do each of these get sent to every stakeholder?
From your example data what do you expect for output?
sh_id is the id of the stakeholder, it links to a contactrecord table which I have not posted here
note_stakeholder_id links to the contactrecord table as well.
the assets are just files that are associated with the stakeholder, a stakeholder can have many assets associated with them
Notes are just notes associated with the stakeholder, a stakeholder can have many notes associated with them.
So is this what you're trying to do?
SELECT c.lastname, c.firstname, n.note
FROM dbo.contactrecord c
INNER JOIN dbo.stakeholder_notes n ON n.note_stakeholder_id = c.stakeholder_id
ORDER BY c.lastname;
January 5, 2016 at 7:26 am
sartis (1/4/2016)
Sean Lange (1/4/2016)
This is much better. Thanks.Now we have the next challenge which I asked previously also. Is there something that states which row(s) from sh_asset belong to a given stakeholder? Or do each of these get sent to every stakeholder?
From your example data what do you expect for output?
sh_id is the id of the stakeholder, it links to a contactrecord table which I have not posted here
note_stakeholder_id links to the contactrecord table as well.
the assets are just files that are associated with the stakeholder, a stakeholder can have many assets associated with them
Notes are just notes associated with the stakeholder, a stakeholder can have many notes associated with them.
Probably too late to change but that is a very poor naming convention. StakeholderID would be much better. It makes is incredibly clear with zero ambiguity.
So I assume then that you are planning on using a StakeHolder table as the basis of your query? From the sample data you posted there are exactly zero notes and assets for the same stakeholder.
We are now on day two of what should have taken roughly 20-30 minutes total to get a fix for. You are not providing enough details for anybody to help you. Keep in mind we can't see your screen, we don't know the project, we don't know the data or the underlying tables. We are just barely starting to get a fuzzy view of what you are trying to accomplish.
Help us to help you by providing the entire issue, not just pieces of it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 5, 2016 at 8:40 am
Sean, Thanks for trying to help. So the page in my web app where I want to display this timeline is called details.cfm . It's a page where I show all details about a stakeholder, in a tab I have a timeline where I show when a stakeholder received/got sent a note for example or an asset, like the details I provided here. I have four different queries currently
1 for notes (my current query for notes)
select * from stakeholder_notes
where note_stakeholder_id = '#URL.id#'
1 for assets - similar query for assets
1 for emails - similar query for emails
1 for events attended - similar query for events_attended
So I know the stakeholder id already and each table has an "id" that links back to my stakeholder table.
The problem with this method is it displays all notes together, all assets together when I output these queries. I would like one query that selects the data for all four of these tables and output would not be grouped together. Hope that makes sense.
So I just asked if there is a way to select data from multiple tables that all have a date column in one query. I'm new to posting to forums like this so I didn't know all the rules. I only posted part of the data/query to try to keep things simple.
Thanks again.
January 5, 2016 at 8:58 am
sartis (1/5/2016)
Sean, Thanks for trying to help. So the page in my web app where I want to display this timeline is called details.cfm . It's a page where I show all details about a stakeholder, in a tab I have a timeline where I show when a stakeholder received/got sent a note for example or an asset, like the details I provided here. I have four different queries currently1 for notes (my current query for notes)
select * from stakeholder_notes
where note_stakeholder_id = '#URL.id#'
1 for assets - similar query for assets
1 for emails - similar query for emails
1 for events attended - similar query for events_attended
So I know the stakeholder id already and each table has an "id" that links back to my stakeholder table.
The problem with this method is it displays all notes together, all assets together when I output these queries. I would like one query that selects the data for all four of these tables and output would not be grouped together. Hope that makes sense.
So I just asked if there is a way to select data from multiple tables that all have a date column in one query. I'm new to posting to forums like this so I didn't know all the rules. I only posted part of the data/query to try to keep things simple.
Thanks again.
I have a suggestion. I'm certainly no expert in Cold Fusion, but I work with people who are and have picked up a few things.
You could create a stored procedure and pass the ID as a parameter. Have the procedure do your individual queries. Because it looks like the queries are going to have different columns in the results, you should separate them. Use a cfquery (??) and cfparam to execute the stored procedure. Take the result set and bind each one of the returned "tables" to the appropriate screen element.
Does this help at all?
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply