help me with division please

• hani wahab

  SSC Journeyman

  Points: 91

  More actions

  June 7, 2006 at 1:43 am

  #168678

  hi, i read the theory abt division it says that we can divide 2 table,

  ex : i have 2 table :

  1.user_companies table, contain of tribute user_id and companies_name

  2.companies table, contain of all companies in the database (select distinct companies_name from user_companies)

  suppose that if we divide the user_companies table and the companies table we get all user_id that works in all companies in the database

  i thought that sql-server2000 has the built in operation of division like minus and union, but it doesn't

  so can u help me....with the query

  thanks, it'll help

• RyanRandall

  SSChampion

  Points: 13623

  More actions

  June 7, 2006 at 4:54 am

  #642223

  Hi hani

  That's called relational division...

  http://www.developersdex.com/gurus/articles/113.asp

  --data

  declare @user_companies table (user_id int, companies_name varchar(10))

  insert @user_companies

            select 1, 'a'

  union all select 1, 'b'

  union all select 2, 'a'

  union all select 2, 'b'

  union all select 2, 'c'

  union all select 3, 'b'

  union all select 4, 'a'

  union all select 4, 'b'

  union all select 4, 'c'

  declare @companies table (companies_name varchar(10))

  insert @companies

            select 'a'

  union all select 'b'

  union all select 'c'

  --calculation

  select user_id from @user_companies

  group by user_id having count(*) = (select count(*) from @companies)

   

  Ryan Randall

  Solutions are easy. Understanding the problem, now, that's the hard part.