recently i was having a problem in my stored procedure, it was working too slow, so one of my colleague suggested cross apply, he said that cross apply work as inner join but without the need of comman key between the table.
my senario is i have three tables crossarticle_article
and crossarticle_articletocategory
, and crossarticle_article
now i want to retreive all the records whose categoryid
is 4, categoryid
are stored in crossarticle_articletocategory
as much i understood, i created below query for it:
SELECT *
FROM crossarticle_article c
CROSS APPLY
crossarticle_articletocategory cc1
CROSS APPLY
crossarticle_category cc2
WHERE cc2.id = 1
this query should return only records from crossarticle_article开发者_如何学Python
where category id matches 1
but it returns all the records from all 3 tables.
where i am going wrong, if this is not the way of using cross apply then how to use, and whats the advantage of it.
is it that cross apply works with same tables... if so than what could be the scenario...
Assuming id is the key field in all tables, The Query should be:
SELECT c.*,
c1.colXXX,
c2.colYYY --ALL columns u need
FROM crossarticle_article c
CROSS APPLY(
SELECT cc1.colXXX
FROM crossarticle_articletocategory cc1
WHERE cc1.id = c.id
) c1
CROSS APPLY(
SELECT cc2.colYYY
FROM crossarticle_category cc2
WHERE cc2.id = c.id
) c2
WHERE c.id = 4
精彩评论