Elliminate Cross Join in the query

Question

Post reply

Elliminate Cross Join in the query

vkundar

Hall of Fame

Points: 3431
More actions
December 10, 2009 at 12:06 am

#228414

Hi,
I have a query which display result from one table ticket_history. My requirement is that I should get all the tickets with pending time and unlock time depending on the ticket state. The query diaplay a cross join result , how to avoide this. There is no key
to join this 2 records as these are from single table.
Query:
==========
Select X.id,Y.id,X.create_time Pending_time,Y.create_time unlock_time
From
(SELECT id,ticket_id,create_time,name,history_type_id,state_id FROM ticket_history h
where ticket_id =1620 and state_id =6 and history_type_id=26 ) X,
(SELECT id,ticket_id,create_time, name,history_type_id,state_id FROM ticket_history h
where ticket_id =1620 and history_type_id=25 and state_id =6 ) Y
Result output:
==========
XID YID Pending time Unlocktime
24757, 24864, '2009-12-02 16:58:43', '2009-12-03 11:15:33'
25141, 24864, '2009-12-07 09:34:44', '2009-12-03 11:15:33'
24757, 25556, '2009-12-02 16:58:43', '2009-12-09 10:12:28'
25141, 25556, '2009-12-07 09:34:44', '2009-12-09 10:12:28'
Thanks
Cheers,
Got an idea..share it !!
DBA_Vishal

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Nabha SSCrazy Eights Points: 8534 More actions · Answer 1

Some test data and table script would help a lot.

But is this what you want?

Select X.id,Y.id,X.create_time Pending_time,Y.create_time unlock_time

From ticket_history X

JOIN ticket_history Y

ON X.ticket_id = Y.ticket_id

AND X.state_id = Y.state_id

Where X.ticket_id = 1620

AND X.state_id = 6

AND (history_type_id = 26 AND history_type_id = 25)

---------------------------------------------------------------------------------

vkundar Hall of Fame Points: 3431 More actions · Answer 2

Thanks for the reply, I want first and last row as output rather then all the four rows.

waiting for your reply..:-)

Cheers,

Got an idea..share it !!

DBA_Vishal

Nabha SSCrazy Eights Points: 8534 More actions · Answer 3

What is the business rule to get your 'first' and 'last' row?

AS suggested earlier, give some test data in a consumable format and what you expect and the rule for that.

---------------------------------------------------------------------------------

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

vkundar (12/10/2009)
Thanks for the reply, I want first and last row as output rather then all the four rows.
waiting for your reply..:-)

But Nabha's query works...

CREATE TABLE #ticket_history (id INT,

ticket_id INT,

create_time DATETIME,

name VARCHAR(10),

history_type_id INT,

state_id INT)

INSERT INTO #ticket_history (id, ticket_id, create_time, name, history_type_id, state_id)

SELECT 24757, 1620, '2009-12-02 16:58:43', 'Fred', 26, 6 UNION ALL

SELECT 25141, 1620, '2009-12-07 09:34:44', 'Fred', 26, 6 UNION ALL

SELECT 24864, 1620, '2009-12-03 11:15:33', 'Fred', 25, 6 UNION ALL

SELECT 25556, 1620, '2009-12-09 10:12:28', 'Fred', 25, 6

-- original query with cross-join

Select X.id,Y.id,X.create_time Pending_time,Y.create_time unlock_time

From

(SELECT id,ticket_id,create_time,name,history_type_id,state_id FROM #ticket_history h

where ticket_id =1620 and state_id =6 and history_type_id=26 ) X,

(SELECT id,ticket_id,create_time, name,history_type_id,state_id FROM #ticket_history h

where ticket_id =1620 and history_type_id=25 and state_id =6 ) Y

-- new query same as Nabha, SAME RESULT as original query

SELECT X.id, Y.id, X.create_time Pending_time, Y.create_time unlock_time

FROM #ticket_history x

INNER JOIN #ticket_history y

ON y.ticket_id = x.ticket_id

AND y.state_id = x.state_id

AND y.history_type_id=25

WHERE x.history_type_id = 26

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden