Creating a table based on a non join ! ! !

• paul.farnell

  SSC Eights!

  Points: 810

  More actions

  October 21, 2014 at 9:24 am

  #294402

  Interesting question, I have 2 tables that I need to merge let me explain. I have a range of product ID's that have a product grouping of * meaning all product groups. So I have a table with products and one with around 100 groups

  ProdID ProdGrp

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

  11 *

  12 *

  ProdGrp ProdGrpDesc

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

  A Prod Group A

  B Prod Group B

  C Prod Group C

  I need a table which looks like the below but I have no joining mechanism

  ProdID ProdGrp

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

  11 A

  11 B

  11 C

  12 A

  12 B

  12 C

  Any ideas ?

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  October 21, 2014 at 9:41 am

  #1753798

  You should try a CROSS JOIN.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  October 21, 2014 at 9:46 am

  #1753803

  It's something similar to a "conditional" cross join:

  DECLARE @Products TABLE (ProdID int, ProdGrp char(1))

  INSERT INTO @Products VALUES (11,'*')

  INSERT INTO @Products VALUES (12,'*')

  DECLARE @ProductGroups TABLE (ProdGrp char(1), ProdGrpDesc varchar(50))

  INSERT INTO @ProductGroups VALUES ('A', 'Prod Group A')

  INSERT INTO @ProductGroups VALUES ('B', 'Prod Group B')

  INSERT INTO @ProductGroups VALUES ('C', 'Prod Group C')

  SELECT P.ProdID, G.ProdGrp

  FROM @Products AS P

  INNER JOIN @ProductGroups AS G

  ON P.ProdGrp = G.ProdGrp

  OR P.ProdGrp = '*'

  ORDER BY P.ProdID, G.ProdGrp

  -- Gianluca Sartori

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  October 21, 2014 at 9:55 am

  #1753808

  Or more generally, using an outer join:

  CREATE TABLE MyProducts (ProdID int, ProdGrp char(7))

  INSERT INTO MyProducts VALUES

  (11, '*')

  ,(12, '*')

  ,(13, 'Group A')

  ,(14, 'Group B')

  ,(15, 'Group C')

  CREATE TABLE MyGroups (ProdGrp char(6), ProdGrpDesc char(7))

  INSERT INTO MyGroups VALUES

  ('A Prod','Group A'),

  ('B Prod','Group B'),

  ('C Prod','Group C')

  SELECT p.ProdID, p.ProdGrp

  FROM MyProducts p

  LEFT JOIN MyGroups g

  ON p.ProdGrp = '*'

  John