| Author |
 Topic  |
|
|
jaimealvarez
Starting Member
29 Posts |
 Posted - 04/24/2012 : 11:15:00
|
I have to sql codes that work fine separetely. I want to see if there is a way to join them:
Code 1:
SELECT tindex,
STUFF((SELECT ' ' + tddesc FROM son_db.dbo.timedesc WHERE tindex = t.tindex ORDER BY tdline FOR XML PATH('')),1,1,'') AS [tddesc]
FROM (SELECT DISTINCT tindex FROM son_db.dbo.timedesc)t
Code 2:
SELECT
client.clnum AS 'Client'
, matter.mmatter AS 'Matter'
, timecard.ttk AS 'TTKID'
, timekeep.tklast + ', ' + timekeep.tkfirst AS 'Timekeeper'
, timekeep.tktitle AS 'Title'
, timecard.tworkdt AS 'WorkedDate'
, timecard.tworkhrs AS 'Hours'
, timecard.tworkdol AS 'WorkedAmount'
, timecard.tstatus AS 'Status'
, timecard.tinvoice AS 'Invoice'
, matter.mcurrency AS 'Currency'
, x.tddesc AS 'DiaryEntry'
FROM
son_db.dbo.client client
, son_db.dbo.matter matter
, son_db.dbo.timecard timecard
, son_db.dbo.timekeep timekeep
, X
WHERE client.clnum = matter.mclient
AND matter.mmatter = timecard.tmatter
AND timecard.ttk = timekeep.tkinit
AND timecard.tindex = x.tindex
AND ((timecard.tprntfl Not In ('R','X'))
AND (timecard.tstatus<>'ADE, D, E, NB, NBP'))
AND (matter.mmatter Like ?)
AND (timecard.ttk Like ?)
AND (timecard.tworkdt>=?
And timecard.tworkdt<=?))
the joint field will be timecard.tindex (from 2nd code) and tindex (from 1st code).
Any way to combine these?
Any help will be appreciated.
Thanks |
|
|
Lamprey
Flowing Fount of Yak Knowledge
3821 Posts |
Posted - 04/24/2012 : 11:28:28
|
You'll have to add tindex to your second query, but here is a generic outline for you to combine them (assuming you want ot join them):SELECT *
FROM
(
-- QUery 1
) AS A
INNER JOIN
(
-- Query 2
) AS B
ON A.tindex = B.tindex |
 |
|
|
jaimealvarez
Starting Member
29 Posts |
Posted - 04/24/2012 : 11:47:22
|
Thanks for the help.
I tried this:
SELECT *
FROM
(
SELECT tindex,
STUFF((SELECT ' ' + tddesc FROM son_db.dbo.timedesc WHERE tindex = t.tindex ORDER BY tdline FOR XML PATH('')),1,1,'') AS [tddesc]
FROM (SELECT DISTINCT tindex FROM son_db.dbo.timedesc)t
)
AS A
INNER JOIN
(
SELECT
client.clnum AS 'Client'
, matter.mmatter AS 'Matter'
, timecard.ttk AS 'TTKID'
, timekeep.tklast + ', ' + timekeep.tkfirst AS 'Timekeeper'
, timekeep.tktitle AS 'Title'
, timecard.tworkdt AS 'WorkedDate'
, timecard.tworkhrs AS 'Hours'
, timecard.tworkdol AS 'WorkedAmount'
, timecard.tstatus AS 'Status'
, timecard.tinvoice AS 'Invoice'
, matter.mcurrency AS 'Currency'
, timecard.tindex AS 'Index'
FROM
son_db.dbo.client client
, son_db.dbo.matter matter
, son_db.dbo.timecard timecard
, son_db.dbo.timekeep timekeep
WHERE
client.clnum = matter.mclient
AND matter.mmatter = timecard.tmatter
AND timecard.ttk = timekeep.tkinit
AND ((timecard.tprntfl Not In ('R','X'))
AND (timecard.tstatus<>'ADE, D, E, NB, NBP'))
)
AS B
ON A.tindex = B.tindex
But I get an error: "Could not add the table '('."
Any ideas?
Thank you. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 04/24/2012 : 21:30:55
|
where are you using this query? is it inside some application? what corresponds to '?' are they parameter place holders?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Help with this code
