Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to create cartesian product sql query.But,I'm getting the error.Where I wrong?

alter table #Personel
(
Ad varchar(100)
)

alter table #Masa
(
 MasaAdi varchar(100)
)

insert into #Personel values('deneme1'),('deneme2'),('deneme3')
insert into #Masa values('Masa1'),('masa2'),('masa3')
select Ad,MasaAdi from
(
   select ROW_NUMBER() over(partition by MasaAdi order by ID,Ad ) as Sira
      ,ID,Ad,MasaAdi
   from (
   select newid() as ID,Ad,MasaAdi from #Personel
   left outer join #Masa on 1=1
   ) t
) t2 where Sira = 1 order by Ad
share|improve this question
    
I tried your code on SQL Server 2008 and got no error. Which DBMS are you using. Do you get an error or do you get the wrong result? –  bernd_k Jun 23 '11 at 14:42
    
If your platform allows, you should make your Cartesian product intent explicit by specifying CROSS JOIN. –  Leigh Riffel Jun 23 '11 at 17:13
    
Technically a CROSS JOIN in SQL isn't the same as the Cartesian Product but CROSS JOIN may be what you want. A comma (,) is the alternative syntax that can be used instead of CROSS JOIN (not all SQL versions support CROSS JOIN but most do support the "comma join" syntax) –  sqlvogel Jun 23 '11 at 17:26
    
@dportas Can you provide a source for Cartesian Product <> CROSS JOIN? –  Leigh Riffel Jun 23 '11 at 20:39

1 Answer 1

up vote 2 down vote accepted

On SQL Server you can write

select Ad,MasaAdi from
(
 select ROW_NUMBER() over(partition by MasaAdi order by ID,Ad ) as Sira,ID,Ad,MasaAdi
 from (
 select newid() as ID,Ad,MasaAdi 
 from #Personel
 cross join #Masa
 )t
 ) t2 where Sira = 1 order by Ad

This is the best syntax to emphasize that it is a cross join.

But on SQL Server 2008 your query gives the same result .

EDIT:

Your problem is not the select statement, but the inserts. Inserts with multiple value clauses is a new feature of SQL Server 2008. For 2005 you have to write:

insert into #Personel values('deneme1')
insert into #Personel values('deneme2')
insert into #Personel values('deneme3')

insert into #Masa values('Masa1')
insert into #Masa values('masa2')
insert into #Masa values('masa3')
share|improve this answer
    
@bernd_k-thanks for your answer.I'm using SQL Server 2005.Here is my error code. Msg 102, Level 15, State 1, Line 10 Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 11 Incorrect syntax near ','. –  Cell-o Jun 24 '11 at 5:48
    
Extended my answer –  bernd_k Jun 24 '11 at 6:28
    
Thank you very much. –  Cell-o Jun 24 '11 at 6:33
    
I have a question that chaught my mind.for example, They are sitting different tables two times some people from personel list. For instance, e.g 1-deneme1 -masa2 , 2-deneme2-masa3 ,3-deneme1-masa1 How to provide to sit down separate to each table of each person? –  Cell-o Jun 24 '11 at 7:36
    
Any suggestions? –  Cell-o Jun 24 '11 at 7:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.