SELECT WHERE IN performance er dårligt? Alternativer?

Det er jo svært, når man ikke kender din database og sql'en.

Du kan måske lave unions af nogle mere simple selects ?

Hvis dine subselects returnerer små datasets fra en stor tabel, er det en mulighed at lave temporære tabeller , så der kun er 1 select mod hele den store tabel og subselects mod den lille.

Ja where in sucks... Og der bør være andre måder at løse opgaven på *GG*

Post din SQL her, det vil gøre det nemmere for os at komme med forslag.

Se om I kan gennemskue den... ellers kan I få en forklaring (den bliver lang!) :-D

WITH prodCatsCTE(prodCatId, prodCatName, prodCatBelongsTo, prodCatOrderNr, depth, sortcol) 
    AS 
    ( 
    SELECT pcs.prodCatId, pcs.prodCatName, pcs.prodCatBelongsTo, pcs.prodCatOrderNr, 0, CAST(pcs.prodCatId AS VARBINARY(900)) 
    FROM prodCats pcs 
    WHERE 
        pcs.prodCatBelongsTo = 0 
    AND 
        prodCatId IN 
        (SELECT prodCatBelongsTo FROM prodCats WHERE prodCatId IN 
            (SELECT prodCatId FROM products WHERE supplierId IN 
                (SELECT supplierId FROM sup_cust_conn WHERE customerId = 5) 
            ) 
        )     
    UNION ALL 
    SELECT p.prodCatId, p.prodCatName, p.prodCatBelongsTo, p.prodCatOrderNr, pc.depth+1, 
    CAST(sortcol + CAST(p.prodCatOrderNr AS BINARY(4)) AS VARBINARY(900)) 
    FROM prodCats p 
    JOIN prodCatsCTE AS pc 
    ON p.prodCatBelongsTo = pc.prodCatId 
    WHERE pc.prodCatId = p.prodCatBelongsTo 
    AND p.prodCatId IN 
            (SELECT pro.prodCatId FROM products pro INNER JOIN prodCats pp ON pro.prodCatId=pp.prodCatId WHERE pro.supplierId IN 
                (SELECT supplierId FROM sup_cust_conn WHERE customerId = 5) 
            AND pp.prodCatBelongsTo = pc.prodCatId) 
    AND 
    pc.prodCatId = 1 
    OR 
    (pc.prodCatId = (SELECT prodCatBelongsTo FROM prodCats WHERE prodCatId = 1 )     
    AND p.prodCatId IN 
            (SELECT pro.prodCatId FROM products pro INNER JOIN prodCats pp ON pro.prodCatId=pp.prodCatId WHERE pro.supplierId IN 
                (SELECT supplierId FROM sup_cust_conn WHERE customerId = 5) 
            AND pp.prodCatBelongsTo = pc.prodCatId))     
    )     
    SELECT 
    REPLICATE('    ', depth) + prodCatName AS prodCatName, prodCatId, prodCatBelongsTo, prodCatOrderNr, depth 
    FROM prodCatsCTE 
    ORDER BY sortcol

SKriv dine where in om til joins i stedet for.
eksempel :
pro.supplierId IN
                (SELECT supplierId FROM sup_cust_conn WHERE customerId = 5) 

til
JOIN  sup_cust_conn s ON  s.supplierId =  pro.supplierId AND s.customerId =5


en not in kan laves med en

LEFT JOIN  sup_cust_conn s ON  s.supplierId =  pro.supplierId AND s.customerId =5
WHERE s.supplierId IS NOT NULL

Så prøvede jeg at joine istedet...

WITH prodCatsCTE(prodCatId, prodCatName, prodCatBelongsTo, prodCatOrderNr, depth, sortcol) 
    AS 
    ( 
    SELECT pcs.prodCatId, pcs.prodCatName, pcs.prodCatBelongsTo, pcs.prodCatOrderNr, 0, CAST(pcs.prodCatId AS VARBINARY(900)) 
    FROM prodCats pcs
        JOIN prodCats p1 ON pcs.prodCatId=p1.prodCatBelongsTo
            JOIN products p2 ON pcs.prodCatId=p2.prodCatId
                JOIN sup_cust_conn s ON p2.supplierId=s.supplierId
    WHERE s.customerId = 5 AND pcs.prodCatBelongsTo = 0 
    UNION ALL 
    SELECT p.prodCatId, p.prodCatName, p.prodCatBelongsTo, p.prodCatOrderNr, pc.depth+1, 
    CAST(sortcol + CAST(p.prodCatOrderNr AS BINARY(4)) AS VARBINARY(900)) 
    FROM prodCats p 
    JOIN prodCatsCTE AS pc 
    ON p.prodCatBelongsTo = pc.prodCatId 
    )     
    SELECT 
    REPLICATE('    ', depth) + prodCatName AS prodCatName, prodCatId, prodCatBelongsTo, prodCatOrderNr, depth 
    FROM prodCatsCTE 
    ORDER BY sortcol

Nu eksekverer den uden problemer, dog er der ingen resultater, hvor der før var følgende:

prodCatName | prodCatId, prodCatBelongsTo, prodCatOrderNr, depth
Skuret    1    0    10    0
    Hammere    4    1    10    1
    Save    5    1    20    1
    River    6    1    30    1
Taget    3    0    30    0

Der har sikkert sneget sig et par fejl ind.

Jeg kunne godt tænke mig du fjernede din union her under debug-fasen. Den kan vi altid få til at virke senere :)

Kan du forsøge med:

WITH prodCatsCTE(prodCatId, prodCatName, prodCatBelongsTo, prodCatOrderNr, depth, sortcol) 
    AS 
    ( 
    SELECT pcs.prodCatId, pcs.prodCatName, pcs.prodCatBelongsTo, pcs.prodCatOrderNr, 0, CAST(pcs.prodCatId AS VARBINARY(900)) 
    FROM prodCats pcs
        inner join prodCats p1
      ON pcs.prodCatId=p1.prodCatBelongsTo
        inner join products p2
      ON pcs.prodCatId=p2.prodCatId
        inner join sup_cust_conn s
      ON p2.supplierId=s.supplierId
    WHERE s.customerId = 5
    AND pcs.prodCatBelongsTo = 0  )


Er der null values i nogle af de felter du joiner op imod? Det må der nemlig ikke være! Så skal du bruge en isnull(p1.prodCatBelongsTo, somethingjadajada) *S*

Vi skal nok få løst det :)

Uhm stadig ingen resultater... Der kan sagtens være null værdier i nogle af rækkerne, for ideen er jo at den kun hiver de relevante resultater ud som ikke er null, men jeg prøvede at sætte "AND IsNull(pcs.prodCatBelongsTo, 0)", men det er jeg ret sikker på er forkert, da jeg ikke er 100% nede med det der IsNull halløj.... :-D

Hvad skal jeg gøre?

hey donkazz...

Ja det er også ok at den kun hiver ud hver der ikke er null, det løses vha. sql-udtrykket.

Jeg vil gerne være helt sikker.. Forsøgte du uden en union? Og så må vi igang med at debugge :-) dvs. tag det her udtryk:

SELECT pcs.prodCatId, pcs.prodCatName, pcs.prodCatBelongsTo, pcs.prodCatOrderNr, 0, CAST(pcs.prodCatId AS VARBINARY(900)) 
    FROM prodCats pcs
        inner join prodCats p1
      ON pcs.prodCatId=p1.prodCatBelongsTo
        inner join products p2
      ON pcs.prodCatId=p2.prodCatId
        inner join sup_cust_conn s
      ON p2.supplierId=s.supplierId
    WHERE s.customerId = 5
    AND pcs.prodCatBelongsTo = 0

og skriv om til:
SELECT pcs.prodCatId, pcs.prodCatName, pcs.prodCatBelongsTo, pcs.prodCatOrderNr, 0, CAST(pcs.prodCatId AS VARBINARY(900)) 
    FROM prodCats pcs
        inner join prodCats p1
      ON pcs.prodCatId=p1.prodCatBelongsTo
--        inner join products p2
--      ON pcs.prodCatId=p2.prodCatId
--        inner join sup_cust_conn s
--      ON p2.supplierId=s.supplierId
    WHERE  pcs.prodCatBelongsTo = 0

Så ser vi lige ad om der overhovedet er noget data der matcher prodcatid = ..belongsto

Hvis der så er, så udfør næste skridt her:
SELECT pcs.prodCatId, pcs.prodCatName, pcs.prodCatBelongsTo, pcs.prodCatOrderNr, 0, CAST(pcs.prodCatId AS VARBINARY(900)) 
    FROM prodCats pcs
        inner join prodCats p1
      ON pcs.prodCatId=p1.prodCatBelongsTo
        inner join products p2
      ON pcs.prodCatId=p2.prodCatId
--        inner join sup_cust_conn s
--      ON p2.supplierId=s.supplierId
    WHERE  pcs.prodCatBelongsTo = 0

Altså test for match imellem prodCatId. Jeg kunne godt tænke mig at se hvor katten det er den fejler?

Ellers må du give lidt data og tabelstruktur så jeg bedre kan se hvad vi taler om hehe....

PS: Du skriver "den kun hiver de relevante resultater ud som ikke er null", kan du evt. gøre sådan her:
ON isnull(p2.supplierId,-1) = isnull(s.supplierId, -2)

Så kom vi lidt videre... :-D

Følgende:
WITH prodCatsCTE(prodCatId, prodCatName, prodCatBelongsTo, prodCatOrderNr, depth, sortcol) 
    AS 
    ( 
SELECT pcs.prodCatId, pcs.prodCatName, pcs.prodCatBelongsTo, pcs.prodCatOrderNr, 0, CAST(pcs.prodCatId AS VARBINARY(900)) 
    FROM prodCats pcs
        inner join prodCats p1
      ON pcs.prodCatId=p1.prodCatBelongsTo
--        inner join products p2
--      ON pcs.prodCatId=p2.prodCatId
--        inner join sup_cust_conn s
--      ON p2.supplierId=s.supplierId
    WHERE  pcs.prodCatBelongsTo = 0 )   
    SELECT 
    REPLICATE('    ', depth) + prodCatName AS prodCatName, prodCatId, prodCatBelongsTo, prodCatOrderNr, depth 
    FROM prodCatsCTE 
    ORDER BY sortcol

Gav

(prodCatName, prodCatId, prodCatBelongsTo, prodCatOrderNr, depth)
Badeværelset    1    0    10    0
Badeværelset    1    0    10    0
Badeværelset    1    0    10    0
Varmesystemer    3    0    30    0
Varmesystemer    3    0    30    0
Varmesystemer    3    0    30    0

Hvor imod:

WITH prodCatsCTE(prodCatId, prodCatName, prodCatBelongsTo, prodCatOrderNr, depth, sortcol) 
    AS 
    ( 
SELECT pcs.prodCatId, pcs.prodCatName, pcs.prodCatBelongsTo, pcs.prodCatOrderNr, 0, CAST(pcs.prodCatId AS VARBINARY(900)) 
    FROM prodCats pcs
        inner join prodCats p1
      ON pcs.prodCatId=p1.prodCatBelongsTo
        inner join products p2
      ON pcs.prodCatId=p2.prodCatId
--        inner join sup_cust_conn s
--      ON p2.supplierId=s.supplierId
    WHERE  pcs.prodCatBelongsTo = 0 )   
    SELECT 
    REPLICATE('    ', depth) + prodCatName AS prodCatName, prodCatId, prodCatBelongsTo, prodCatOrderNr, depth 
    FROM prodCatsCTE 
    ORDER BY sortcol

Gav ingen resultater.. :-o

Giver det mening?

hey.. beklager min sene kommentar.

hmm, er du sikker på at prodCats.prodCatId = 0 også findes i tabellen products.prodCatId ?

Altså hvis prodCats.prodCatId = 0, så eksisterer produktkategorien ikke. Hvis prodCats.prodCatBelongsTo = 0, så er produktkategorien top level.
products.prodCatId vil altid være forskellig fra 0, for hvis en produktkategori bliver slettet, så skal produkterne alligevel ikke vises.

Nu prøvede jeg lige med:

WITH prodCatsCTE(prodCatId, prodCatName, prodCatBelongsTo, prodCatOrderNr, depth, sortcol) 
    AS 
    ( 
SELECT pcs.prodCatId, pcs.prodCatName, pcs.prodCatBelongsTo, pcs.prodCatOrderNr, 0, CAST(pcs.prodCatId AS VARBINARY(900)) 
    FROM prodCats pcs
        inner join prodCats p1
      ON pcs.prodCatId=p1.prodCatBelongsTo
        inner join products p2
      ON p1.prodCatId=p2.prodCatId
--        inner join sup_cust_conn s
--      ON p2.supplierId=s.supplierId
    WHERE  pcs.prodCatBelongsTo = 0 )   
    SELECT 
    REPLICATE('    ', depth) + prodCatName AS prodCatName, prodCatId, prodCatBelongsTo, prodCatOrderNr, depth 
    FROM prodCatsCTE 
    ORDER BY sortcol


Hvor jeg laver en inner join på p1.prodCatId=p2.prodCatId i stedet for pcs.prodCatId=p2.prodCatId, hvilket også giver mere mening, da p1 er den prodCat som products tilhører, hvor pcs er den prodCat som p1 tilhører. Dvs. produkter aldrig tilhører top level prodCats.

Til gengæld får jeg hver top level prodCat samme antal gange som det antal produkter der i sidste ende ligger under. :(
Badeværelset    1    0    10    0
Badeværelset    1    0    10    0
Badeværelset    1    0    10    0
Badeværelset    1    0    10    0
Badeværelset    1    0    10    0
Badeværelset    1    0    10    0
Badeværelset    1    0    10    0
Badeværelset    1    0    10    0
Badeværelset    1    0    10    0
Varmesystemer    3    0    30    0

ahh nu er det måske ved at dæmre *GG*

Jeg tror vi har lavet en join fejl fra starten af.

WITH prodCatsCTE(prodCatId, prodCatName, prodCatBelongsTo, prodCatOrderNr, depth, sortcol) 
    AS 
    ( 
SELECT pcs.prodCatId, pcs.prodCatName, pcs.prodCatBelongsTo, pcs.prodCatOrderNr, 0, CAST(pcs.prodCatId AS VARBINARY(900)) 
    FROM prodCats pcs
inner join (SELECT prodCatBelongsTo FROM prodCats p1_inner
        inner join products p2
            on p2.prodCatId = p1_inner.prodCatId
        inner join sup_cust_conn s
            on s.supplierId = p2.supplierId
                where s.customerId = 5) as p
on p.prodCatBelongsTo = pcs.prodCatId
)
SELECT 
    REPLICATE('    ', depth) + prodCatName AS prodCatName, prodCatId, prodCatBelongsTo, prodCatOrderNr, depth 
    FROM prodCatsCTE 
    ORDER BY sortcol



Nu føler jeg mere at joinet er det samme som alle de where-in's :)
Det hele kom sig af at vi tidligt i joinet begrænsede det ved kun at tillade prodCatBelongsTo = 0 og det er jo ikke tilfældet at vi ønsker den begrænsning når vi gerne vil kunne benytte den som sub join.

Well.. prøv lige det der så. pyhaaa tager sørme tid ;-)

Tillad mig at citere dig:
"Vi skal nok få løst det :)"

;-))

Men du har ret, det tager sin tid! :-D Det er også ubetinget den længste SQL jeg nogensinde har rodet med og det må siges at være jomfrurejsen fra de standard SELECT FROM WHERE sætninger jeg hidtil har rodet med. :))

Nå anyways, ovenstående giver:
Badeværelset    1    0    10    0
Badeværelset    1    0    10    0
Badeværelset    1    0    10    0
Badeværelset    1    0    10    0
Badeværelset    1    0    10    0
Varmesystemer    3    0    30    0

Altså man kunne jo være flabet at lave en distinct
WITH prodCatsCTE(prodCatId, prodCatName, prodCatBelongsTo, prodCatOrderNr, depth, sortcol) 
    AS 
    ( 
SELECT pcs.prodCatId, pcs.prodCatName, pcs.prodCatBelongsTo, pcs.prodCatOrderNr, 0, CAST(pcs.prodCatId AS VARBINARY(900)) 
    FROM prodCats pcs
inner join (SELECT prodCatBelongsTo FROM prodCats p1_inner
        inner join products p2
            on p2.prodCatId = p1_inner.prodCatId
        inner join sup_cust_conn s
            on s.supplierId = p2.supplierId
                where s.customerId = 5) as p
on p.prodCatBelongsTo = pcs.prodCatId
)
SELECT DISTINCT
    REPLICATE('    ', depth) + prodCatName AS prodCatName, prodCatId, prodCatBelongsTo, prodCatOrderNr, depth 
    FROM prodCatsCTE 
    ORDER BY sortcol

Men det er ikke den fedeste løsning, årsagen til at joinet giver så mange badeværelser er fordi der er en eller flere kolonner der ikke joines på.

Prøv at udføre denne her:
SELECT *
    FROM prodCats pcs
inner join (SELECT prodCatBelongsTo FROM prodCats p1_inner
        inner join products p2
            on p2.prodCatId = p1_inner.prodCatId
        inner join sup_cust_conn s
            on s.supplierId = p2.supplierId
                where s.customerId = 5) as p
on p.prodCatBelongsTo = pcs.prodCatId

Og poste det, her burde vi meget gerne kunne se hvilke unikke rækker der eksisterer og så må vi se om vi kan eliminere dem :)

Nå nu hiv den også produktbeskrivelsen med ud, så jeg kunne sgu ik lige copy paste det ind til dig.

Kan du se det her?
http://imageshack.dk//viewimage.php?file=/imagesfree/fqY68181.jpg

Hej..
Jo jeg kan godt se det :) Ja tanken er jo at debugge join og når der opstår dubletter kan man lave en * og der skulle man meget gerne kunne se forskelligheder i hver række hehe... men jeg kan ikke lige se dem her, må jeg sige hmm...

Okay, kunne jeg få dig til at lave en SELECT *
    FROM prodCats pcs
inner join (SELECT prodCatBelongsTo FROM prodCats p1_inner
        inner join products p2
            on p2.prodCatId = p1_inner.prodCatId
        inner join sup_cust_conn s
            on s.supplierId = p2.supplierId
                where s.customerId = 5) as p
on p.prodCatBelongsTo = pcs.prodCatId

Og så se om den kommer med samme resultat?

Hvad giver denne her?:
SELECT prodCatBelongsTo FROM prodCats p1_inner
        inner join products p2
            on p2.prodCatId = p1_inner.prodCatId
        inner join sup_cust_conn s
            on s.supplierId = p2.supplierId
                where s.customerId = 5

Hvis du vil må du gerne poste scriptet til at lave de 3 tabeller med.. så kan jeg selv fylde noget data ind og forsøge med :)

Den første gav samme resultater, den anden gav bare nogle cifre, men samme smøre i virkeligheden.

prodCats:
USE [localTest]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[prodCats](
    [prodCatId] [int] IDENTITY(1,1) NOT NULL,
    [prodCatName] [varchar](50) NULL,
    [prodCatContent] [varchar](max) NULL,
    [prodCatBelongsTo] [int] NULL,
    [prodCatOrderNr] [int] NULL,
    [disabled] [bit] NULL CONSTRAINT [DF_prodCats_disabled]  DEFAULT ((0)),
CONSTRAINT [PK_prodCats] PRIMARY KEY CLUSTERED
(
    [prodCatId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF

products:
USE [localTest]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[products](
    [productId] [int] IDENTITY(1,1) NOT NULL,
    [productName] [varchar](50) NULL,
    [shortText] [varchar](200) NULL,
    [orderNr] [int] NULL,
    [prodCatId] [int] NULL,
    [supplierId] [int] NULL,
    [disabled] [bit] NULL CONSTRAINT [DF_products_disabled]  DEFAULT ((0))
CONSTRAINT [PK_products] PRIMARY KEY CLUSTERED
(
    [productId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF

suppliers:
USE [localTest]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[suppliers](
    [supplierId] [int] IDENTITY(1,1) NOT NULL,
    [supplierName] [varchar](50) NULL,
    [disabled] [bit] NULL CONSTRAINT [DF_suppliers_disabled]  DEFAULT ((0)),
CONSTRAINT [PK_suppliers] PRIMARY KEY CLUSTERED
(
    [supplierId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF

sup_cust_conn:
USE [localTest]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sup_cust_conn](
    [supCustConnId] [int] IDENTITY(1,1) NOT NULL,
    [supplierId] [int] NULL,
    [customerId] [int] NULL,
CONSTRAINT [PK_sup_cust_conn] PRIMARY KEY CLUSTERED
(
    [supCustConnId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[sup_cust_conn]  WITH CHECK ADD  CONSTRAINT [FK_sup_cust_conn_customers] FOREIGN KEY([customerId])
REFERENCES [dbo].[customers] ([customerId])
GO
ALTER TABLE [dbo].[sup_cust_conn] CHECK CONSTRAINT [FK_sup_cust_conn_customers]
GO
ALTER TABLE [dbo].[sup_cust_conn]  WITH CHECK ADD  CONSTRAINT [FK_sup_cust_conn_suppliers] FOREIGN KEY([supplierId])
REFERENCES [dbo].[suppliers] ([supplierId])
GO
ALTER TABLE [dbo].[sup_cust_conn] CHECK CONSTRAINT [FK_sup_cust_conn_suppliers]


Var det det du skulle bruge?

Okay.. jeg har indsat noget testdata:
insert into prodCats values(3, 'Varmesystemer', 'content1', 0, 30, 0)
insert into prodCats values(1, 'Badeværelset', 'content2', 0, 10, 0)

insert into products values(1000, 'Oliefyr', 'of', 10, 3, 1, 0)
insert into products values(900, 'Vandvarmer', 'vv', 20, 3, 2, 0)
insert into products values(700, 'Vandhane', 'vh', 10, 1, 1, 0)

insert into sup_cust_conn (supplierid, customerid) values(5, 5)

Det eneste jeg lige kan komme på er fordi den innerste joiner op på products og så er det jo egentlig klart nok at den giver så mange rækker, 1 række for hvert produkt.

SELECT *
    FROM prodCats pcs
inner join (SELECT distinct prodCatBelongsTo FROM prodCats p1_inner
        inner join products p2
            on p2.prodCatId = p1_inner.prodCatId
        inner join sup_cust_conn s
            on s.supplierId = p2.supplierId
                where s.customerId = 5) as p
on p.prodCatBelongsTo = pcs.prodCatId

Jeg vil mene du skal bruge en distinct på p1_inner clausen, det er må betragtes som et af de tilfælde hvor distinct er tilladt, det sker til alt held kun på en integer kolonne hvilket ikke burde give anledning til performance issues...

Dvs. det skal se sådan her ud:
WITH prodCatsCTE(prodCatId, prodCatName, prodCatBelongsTo, prodCatOrderNr, depth, sortcol) 
    AS 
    ( 
SELECT pcs.prodCatId, pcs.prodCatName, pcs.prodCatBelongsTo, pcs.prodCatOrderNr, 0, CAST(pcs.prodCatId AS VARBINARY(900)) 
    FROM prodCats pcs
inner join (SELECT DISTINCT prodCatBelongsTo FROM prodCats p1_inner
        inner join products p2
            on p2.prodCatId = p1_inner.prodCatId
        inner join sup_cust_conn s
            on s.supplierId = p2.supplierId
                where s.customerId = 5) as p
on p.prodCatBelongsTo = pcs.prodCatId
)
SELECT REPLICATE('    ', depth) + prodCatName AS prodCatName, prodCatId, prodCatBelongsTo, prodCatOrderNr, depth 
    FROM prodCatsCTE 
    ORDER BY sortcol

Jeg tror ikke jeg kan komme det meget nærmere, endsige bedre :)

Du kan evt. teste forskellen imellem do. og så din where in... skriv i din query analyzer:
SET STATISTICS IO ON
og sammenlign :-)


Håber du kan bruge det.

Aaaah.... Perfekt!! Det virker jo! :-))

Så skal vi bare have koblet den der union all på som vi fjernede i starten.. ;)

    UNION ALL 
    SELECT p.prodCatId, p.prodCatName, p.prodCatBelongsTo, p.prodCatOrderNr, pc.depth+1, 
    CAST(sortcol + CAST(p.prodCatOrderNr AS BINARY(4)) AS VARBINARY(900)) 
    FROM prodCats p 
    JOIN prodCatsCTE AS pc 
    ON p.prodCatBelongsTo = pc.prodCatId 
    WHERE pc.prodCatId = p.prodCatBelongsTo 
    AND p.prodCatId IN 
            (SELECT pro.prodCatId FROM products pro INNER JOIN prodCats pp ON pro.prodCatId=pp.prodCatId WHERE pro.supplierId IN 
                (SELECT supplierId FROM sup_cust_conn WHERE customerId =  @customerId) 
            AND pp.prodCatBelongsTo = pc.prodCatId) 
    AND 
    pc.prodCatId =  @selectedId 
    OR 
    (pc.prodCatId = (SELECT prodCatBelongsTo FROM prodCats WHERE prodCatId =  @selectedId )     
    AND p.prodCatId IN 
            (SELECT pro.prodCatId FROM products pro INNER JOIN prodCats pp ON pro.prodCatId=pp.prodCatId WHERE pro.supplierId IN 
                (SELECT supplierId FROM sup_cust_conn WHERE customerId =  @customerId) 
            AND pp.prodCatBelongsTo = pc.prodCatId))     
    )

:D

arrghh fuck.. den havde jeg lykkeligt glemt hehe. Men jeg forstår det hele meget bedre nu, håber jeg da.

Well.. men den skal jo næsten også omskrives til joins. Kunne vi starte med:

SELECT p.prodCatId, p.prodCatName, p.prodCatBelongsTo, p.prodCatOrderNr, pc.depth+1, 
    CAST(sortcol + CAST(p.prodCatOrderNr AS BINARY(4)) AS VARBINARY(900)) 
    FROM prodCats p 
    INNER JOIN prodCatsCTE AS pc 
    ON p.prodCatBelongsTo = pc.prodCatId 
    AND pc.prodCatId = p.prodCatBelongsTo 
    INNER JOIN (SELECT pro.prodCatId FROM products pro
                INNER JOIN prodCats pp
                    ON pro.prodCatId=pp.prodCatId
                INNER JOIN sup_cust_conn scc
                    ON scc.supplierId = pro.supplierId
                    WHERE customerId =  5) as p_inner
    ON p_inner.prodCatId = pp.prodCatBelongsTo

Og så se hvad den giver.

Jeg har ikke lige create table til prodCatsCTE.

Jeg rettede sidste linje og så virker det sådan nogenlunde, men hvor fletter jeg @selectedId ind henne? Hvis nu @selectedId = 1 så skal den jo udvide "1 Badeværelset" så man kan se de dertilhørende prodCats samtidigt med at man stadig skal kunne se de andre prodCats where prodCatBelongsTo = 0... :-O

Jeg prøvede den her som NÆSTEN virker efter hensigten, dog kommer prodCats p ud tilsvarende antal produkter prodCat'en har...

WITH prodCatsCTE(prodCatId, prodCatName, prodCatBelongsTo, prodCatOrderNr, depth, sortcol) 
    AS 
    ( 
SELECT pcs.prodCatId, pcs.prodCatName, pcs.prodCatBelongsTo, pcs.prodCatOrderNr, 0, CAST(pcs.prodCatId AS VARBINARY(900)) 
    FROM prodCats pcs
inner join (SELECT DISTINCT prodCatBelongsTo FROM prodCats p1_inner
        inner join products p2
            on p2.prodCatId = p1_inner.prodCatId
        inner join sup_cust_conn s
            on s.supplierId = p2.supplierId
                where s.customerId = 5) as p
on p.prodCatBelongsTo = pcs.prodCatId
WHERE pcs.prodCatBelongsTo = 0
UNION ALL
SELECT p.prodCatId, p.prodCatName, p.prodCatBelongsTo, p.prodCatOrderNr, pc.depth+1, 
    CAST(sortcol + CAST(p.prodCatOrderNr AS BINARY(4)) AS VARBINARY(900)) 
    FROM prodCats p 
    INNER JOIN prodCatsCTE AS pc 
    ON p.prodCatBelongsTo = pc.prodCatId
    INNER JOIN products pro ON p.prodCatId=pro.prodCatId
    INNER JOIN sup_cust_conn scc ON pro.supplierId=scc.supplierID
p.prodCatId = p_inner.prodCatId
    WHERE pc.prodCatId = p.prodCatBelongsTo AND scc.customerId = 5
)
SELECT REPLICATE('    ', depth) + prodCatName AS prodCatName, prodCatId, prodCatBelongsTo, prodCatOrderNr, depth 
    FROM prodCatsCTE 
    ORDER BY sortcol

Giver følgende:

Badeværelset    1    0    10    0
    Sanitet    4    1    10    1
    Armaturer & Batterier    5    1    20    1
    Armaturer & Batterier    5    1    20    1
    Armaturer & Batterier    5    1    20    1
    Brus & Kar    6    1    30    1
Varmesystemer    3    0    30    0
    Gaskedler    16    3    10    1


:-O ?

Selvfølgelig virker det med følgende til sidst

SELECT DISTINCT REPLICATE('    ', depth) + prodCatName AS prodCatName, sortcol, prodCatId, prodCatBelongsTo, prodCatOrderNr, depth 
    FROM prodCatsCTE 
    ORDER BY sortcol

men var der noget med at DISTINCT var fy fy? :-D

Ja vi vil helst undgå distinct på flere kolonner, det er ok at anvende distinct på en enkelt kolonne og helst på int, smallint osv.

Jeg kunne godt tænke mig vi holdt fast i fidusen med et sub inner join products, årsagen er at der kan være mange produkter med samme prodCatId og det vil nemlig give dubletter.

Så kunne du forsøge at arbejde videre med den her fra tidligere? (jeg har tilføjet DISTINCT til subjoin.

SELECT p.prodCatId, p.prodCatName, p.prodCatBelongsTo, p.prodCatOrderNr, pc.depth+1, 
    CAST(sortcol + CAST(p.prodCatOrderNr AS BINARY(4)) AS VARBINARY(900)) 
    FROM prodCats p 
    INNER JOIN prodCatsCTE AS pc 
    ON p.prodCatBelongsTo = pc.prodCatId 
      AND pc.prodCatId =  @selectedId
    INNER JOIN (SELECT DISTINCT pro.prodCatId FROM products pro
                INNER JOIN prodCats pp
                    ON pro.prodCatId=pp.prodCatId
                INNER JOIN sup_cust_conn scc
                    ON scc.supplierId = pro.supplierId
                    WHERE customerId = 5) as p_inner
    ON p_inner.prodCatId = pp.prodCatBelongsTo



Jeg vil mene @selectedId skal stå der :) , det er ikke nemt uden data det her hehe...

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "pp.prodCatBelongsTo" could not be bound.
Msg 460, Level 16, State 1, Line 1
DISTINCT operator is not allowed in the recursive part of a recursive common table expression 'prodCatsCTE

Hvad betyder det? :-O

En lille fejl havde sneget sig ind, jeg skrev pp. der skal stå p

SELECT p.prodCatId, p.prodCatName, p.prodCatBelongsTo, p.prodCatOrderNr, pc.depth+1, 
    CAST(sortcol + CAST(p.prodCatOrderNr AS BINARY(4)) AS VARBINARY(900)) 
    FROM prodCats p 
    INNER JOIN prodCatsCTE AS pc 
    ON p.prodCatBelongsTo = pc.prodCatId 
      AND pc.prodCatId =  @selectedId
    INNER JOIN (SELECT DISTINCT pro.prodCatId FROM products pro
                INNER JOIN prodCats pp
                    ON pro.prodCatId=pp.prodCatId
                INNER JOIN sup_cust_conn scc
                    ON scc.supplierId = pro.supplierId
                    WHERE customerId = 5) as p_inner
    ON p_inner.prodCatId = p.prodCatBelongsTo

Den bliver ved med at sige:

DISTINCT operator is not allowed in the recursive part of a recursive common table expression 'prodCatsCTE'.

Hmmm... hvad faen?

uha.. havde helt glemt denne tråd :-( Beklager...

Ja... hvad er det da for noget, jeg tror den bedste måde at løse det på må være at du sender mig table definition på prodCatsCTE


God Jul :-)

Altså prodCatsCTE er jo den første tabel vi definerer, er det ikke? Det er ikke en jeg har liggende i databasen, så det må være en temp tabel eller noget..?

Ja god Jul og godt nytår ;)

Janus? :)