June 1, 2009 at 9:40 am
The company does not want cross joins so how can I fix the following:
ALTER PROCEDURE [dbo].[sprGetAffectedServicesByTicketID]
(
@TicketID int
)
AS
BEGIN
SELECT
tbl_index_ticket_services.ServiceID,
tbl_lookup_type_services.ID,
tbl_lookup_type_services.ServiceType,
tbl_index_ticket_services.DateAdded,
tbl_index_ticket_services.AgentAdded,
tbl_services.ServiceIdentifier
FROM
tbl_services INNER JOIN
tbl_index_ticket_services ON tbl_services.ServiceID = tbl_index_ticket_services.ServiceID CROSS JOIN
tbl_lookup_type_services WHERE
tbl_index_ticket_services.TicketID = @TicketID and
tbl_index_ticket_services.FlagRemoved <> '1'
ORDER BY tbl_index_ticket_services.DateAdded ASC
June 1, 2009 at 9:49 am
- Why do you think you need the cross join ??
- What's the relationship of that x-joined table to the other tables ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 1, 2009 at 9:49 am
Specify a join condition between either tbl_services and tbl_lookup_type_services or between tbl_index_ticket_services and tbl_lookup_type_services.
How do those tables relate to each other?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 1, 2009 at 9:58 am
I need to joing on service ID.
SELECT tbl_index_ticket_services.ServiceID, tbl_lookup_type_services.ID, tbl_lookup_type_services.ServiceType, tbl_index_ticket_services.DateAdded,
tbl_index_ticket_services.AgentAdded, tbl_services.ServiceIdentifier
FROM tbl_services INNER JOIN
tbl_index_ticket_services ON tbl_services.ServiceID = tbl_index_ticket_services.ServiceID CROSS JOIN
tbl_lookup_type_services where
tbl_index_ticket_services.TicketID = @TicketID and
tbl_index_ticket_services.FlagRemoved '1'
ORDER BY tbl_index_ticket_services.DateAdded
June 1, 2009 at 10:02 am
Like this?
SELECT
tbl_index_ticket_services.ServiceID,
tbl_lookup_type_services.ID,
tbl_lookup_type_services.ServiceType,
tbl_index_ticket_services.DateAdded,
tbl_index_ticket_services.AgentAdded,
tbl_services.ServiceIdentifier
FROM tbl_services
INNER JOIN tbl_index_ticket_services ON tbl_services.ServiceID = tbl_index_ticket_services.ServiceID
INNER JOIN tbl_lookup_type_services ON tbl_services.ServiceID = tbl_lookup_type_services.ServiceID
WHERE tbl_index_ticket_services.TicketID = @TicketID and
tbl_index_ticket_services.FlagRemoved '1'
ORDER BY tbl_index_ticket_services.DateAdded
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 1, 2009 at 10:12 am
This is awesome how come in query analyzer I get the cross join.
June 1, 2009 at 10:32 am
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].sprGetAffectedServicesByTicketID') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].sprGetAffectedServicesByTicketID
GO
CREATE PROCEDURE [dbo].[sprGetAffectedServicesByTicketID]
(
@TicketID int
)
AS
BEGIN
SELECT
tbl_index_ticket_services.ServiceID,
tbl_lookup_type_services.ID,
tbl_lookup_type_services.ServiceType,
tbl_index_ticket_services.DateAdded,
tbl_index_ticket_services.AgentAdded,
tbl_services.ServiceIdentifier
FROM tbl_services
INNER JOIN tbl_index_ticket_services ON tbl_services.ServiceID = tbl_index_ticket_services.ServiceID
WHERE tbl_index_ticket_services.TicketID = @TicketID and
tbl_index_ticket_services.FlagRemoved '1'
ORDER BY tbl_index_ticket_services.DateAdded
END
GO
Msg 4104, Level 16, State 1, Procedure sprGetAffectedServicesByTicketID, Line 8
The multi-part identifier "tbl_lookup_type_services.ServiceType" could not be bound.
June 1, 2009 at 10:33 am
I don't understand your question. If there's a cross join it's because either you typed CROSS JOIN or, if you used a query builder, because you didn't define a relationship between the tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 1, 2009 at 10:41 am
mathieu_cupryk (6/1/2009)
Msg 4104, Level 16, State 1, Procedure sprGetAffectedServicesByTicketID, Line 8The multi-part identifier "tbl_lookup_type_services.ServiceType" could not be bound.
Take a look at my query, take a look at yours. There's a difference.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 1, 2009 at 10:45 am
INNER JOIN tbl_index_ticket_services ON tbl_services.ServiceID = tbl_index_ticket_services.ServiceID
INNER JOIN tbl_lookup_type_services ON tbl_services.ServiceID = tbl_lookup_type_services.ServiceID
Msg 207, Level 16, State 1, Procedure sprGetAffectedServicesByTicketID, Line 26
Invalid column name 'ServiceID'.
June 1, 2009 at 10:47 am
It should be INNER JOIN tbl_lookup_type_services ON tbl_services.ServiceID = tbl_lookup_type_services.ServiceID
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply