Return data from 2 tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Return data from 2 tables

Author

Topic

Steve2106
Posting Yak Master

183 Posts

Posted - 2013-09-06 : 05:19:25

Hi There,

In need of your help again.

I have 2 tables, LiveData & ArchiveData.
At the moment I return values into a grid based on criteria the user selects on a form and I build the query in a stored procedure which queries the LiveData table.
This all works fine but I am now being asked to get the records from the ArchiveData too that match the criteria.
How can I achieve this.

Thanks for any help you can give.

Best Regards,

Steve

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-06 : 07:43:08

Hi Steve,
While getting data write query as follows...
SELECT ColumnNamesWhichYouWantFronBothTables
FROM LiveData L, ArchiveData A
WHERE <Your Conditions>

if you post the table structure and output column names along with your conditions we will provide you exact query

--
Chandu

Steve2106
Posting Yak Master

183 Posts

Posted - 2013-09-06 : 09:59:21

Hi Bandi,

Thanks for your reply.
Below is the query I am using to get the data from 1 table.

SELECT
LiveData.ActionId,
LiveData.Description,
LiveData.ResponsibleId,
LiveDataOrg.LastName + ', ' + LiveDataOrg.FirstName As RPFullName,
LiveData.ActioneeId,
LiveDataOrg1.LastName + ', ' + LiveDataOrg1.FirstName As ActioneeFullName,
LiveData.TypeId,
LiveDataTypes.TypeDescription,
LiveData.Risk,
LiveDataRisk.Description As RiskDescription,
LiveData.DateRaised,
LiveData.PlannedStartDate,
LiveData.ActualStartDate,
LiveData.PlannedCompletionDate,
LiveData.ActualCompletionDate,
LiveData.EditorComments,
LiveData.EditorActions,
LiveData.SourceId,
LiveDataSource.SourceDescription,
LiveData.PerComplete,
LiveData.LocationId,
LiveDataLocation.Location,
LiveDataLocation.Abbriviation,
Count(LiveDataImages.ActionId) As NumDocs
From
LiveData Inner Join
LiveDataOrg On LiveData.ResponsibleId = LiveDataOrg.PersonnelId Inner Join
LiveDataOrg LiveDataOrg1 On LiveData.ActioneeId = LiveDataOrg1.PersonnelId Inner Join
LiveDataTypes On LiveData.TypeId = LiveDataTypes.TypeId Inner Join
LiveDataRisk On LiveData.Risk = LiveDataRisk.RiskId Inner Join
LiveDataSource On LiveData.SourceId = LiveDataSource.SourceId Inner Join
LiveDataLocation On LiveData.LocationId = LiveDataLocation.LocationId LEFT JOIN
LiveDataImages ON LiveData.ActionId=LiveDataImages.ActionId
Where LiveData.SourceId = 26
Group By LiveData.ActionId,
LiveData.Description,
LiveData.ResponsibleId,
LiveDataOrg.FirstName,
LiveDataOrg.LastName,
LiveData.ActioneeId,
LiveDataOrg1.FirstName,
LiveDataOrg1.LastName,
LiveData.TypeId,
LiveDataTypes.TypeDescription,
LiveData.Risk,
LiveDataRisk.Description,
LiveData.DateRaised,
LiveData.PlannedStartDate,
LiveData.ActualStartDate,
LiveData.PlannedCompletionDate,
LiveData.ActualCompletionDate,
LiveData.EditorComments,
LiveData.EditorActions,
LiveData.SourceId,
LiveDataSource.SourceDescription,
LiveData.PerComplete,
LiveData.LocationId,
LiveDataLocation.Location,
LiveDataLocation.Abbriviation

Thanks for your help.

Best Regards,

Steve

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-06 : 12:25:55

It depends on how your data is structured and how you want to return it. I would not use Bandi's suggestion as I prefer ANSI style joins. But, my assumption due to lack of requirements, is that you could run two selects and union them together to get one combined result set.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources