how to display category name for c and x from table category without using self join?



  • I work in sql server 2014 I need to get categories c and x without using self join.

    But, I don't know how to do that.

    My data sample

    create table #category
     (
     categoryc  int,
     categoryx int
     )
     insert into #category(categoryc,categoryx)
     values
     (19,20),
     (50,75),
     (80,70)
    

    create table #categorydetails
    (
    categoryid int,
    categoryname nvarchar(300)
    )
    insert into #categorydetails(categoryid,categoryname)
    values
    (19,'bmw'),
    (20,'mercedees'),
    (50,'feat'),
    (75,'toyota'),
    (80,'mazda'),
    (70,'suzoky')

    select d1.categoryname as categoryc, d2.categoryname as categoryx
    from #category c
    left join #categorydetails d1 on d1.categoryid=c.categoryc
    left join #categorydetails d2 on d2.categoryid=c.categoryx

    expected result

    display category c and x without self join



  • Could be done using CROSS APPLY?

     SELECT
     d1.categoryname AS categoryc
     ,d2.categoryname AS categoryx
     FROM #category c
     CROSS APPLY #categorydetails AS d1
     CROSS APPLY #categorydetails AS d2
     WHERE d1.categoryid=c.categoryc and d2.categoryid=c.categoryx;
    

    By the way, in your query there is no self join. Self join is when you do TableA AS Ta1 Join TableA AS Ta2 on Ta1.x = Ta1.y. In your query you are simply doing two join between category and details on two different fileds.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2