how to get categories c and x from table category without using self join ?

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  February 1, 2022 at 6:17 am

  #3979441

  i work on sql server 2012 i need to get categories c and x without using self join

  but i don't know how to make 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 as below :

   

  

   

  so how to get expected data above without using self join

  are there are another way to do that without using self join

• ratbak

  Ten Centuries

  Points: 1379

  More actions

  February 1, 2022 at 3:25 pm

  #3979629

  This seems like a non-normalized design. Is there a relationship between categoryc  & categoryx in the same row? What is it?

  If not (and probably even if there is), use a category type column rather than separate columns for the two types. And the only apparent reason for #categorydetails is the implementation of #category. categoryname should just be another column in #category along with categorytype.

  If categoryc & categoryx are related, it would be better to have a separate association table to define that relationship, but still use a normalized category table that only defines a category, not it's relationship to other categories.

  e.g.,

  DROP TABLE IF EXISTS #category;
  CREATE table #category
   (
   categoryid INT NOT NULL PRIMARY KEY,
   categorytype CHAR(1) NOT NULL,
   categoryname varchar(30)
   );
  DROP TABLE IF EXISTS #categoryassocation;
  CREATE table #categoryassocation
   (categoryid INT NOT NULL,        -- I don't like using c & x here -- Can one c be related to another c or x to another x? Or maybe there will be new types?
    relatedcategoryid INT NOT NULL, -- But if you're certain those are the only categories that will exist, and c-to-x is the only relationship that will exist, you can name them more explicitly.
    PRIMARY KEY CLUSTERED (categoryid,relatedcategoryid)
    );
  
  insert into #category(categoryid,categorytype,categoryname)
  values
   (19,'c','bmw'),
   (20,'x','mercedees'),
   (50,'c','feat'),
   (75,'x','toyota'),
   (80,'c','mazda'),
   (70,'x','suzoky');
  
  INSERT INTO #categoryassocation (categoryid,relatedcategoryid)
  VALUES 
   (19,20),
   (50,75),
   (80,70);
  
  select c.categoryname as categoryc,x.categoryname as categoryx 
  FROM #categoryassocation ca
  left join #category c on c.categoryid=ca.categoryid
  left join #category x on x.categoryid=ca.relatedcategoryid
  ORDER BY categoryc, categoryx;
  
  

  Note: You're using two left joins, not a self join (joining a table to itself).

  I followed your example for consistency but all lower-case names are very difficult to read -- please either used mixed case, or put underscores between words.