sqlpostgresqlcross-joinnatural-join

SQL/PSQL Referencing column names in the where clause after cross joining the same table with each other


I'm trying to figure out how to reference certain columns from a cross join when you cross join a table with itself. The Cross join table as a whole is also given an alias, which is why I'm having trouble accessing the column names because it is also natural-joined with another subquery. Here's the code:

select q0.sid, q0.sname 
from (buys t1 cross join student s3 cross join student s4) q0 natural join 
     (select s5.sid, s5.sname, s6.sid, s6.sname, t3.sid, t3.bookno                                                                      
      from buys t2 cross join student s5 cross join student s6 cross join buys t3                                                                       
      where t2.sid = s5.sid and t3.bookno = t2.bookno) q1
where t1.sid = s3.sid;

The issue is that 'where' clause of trying to compare t1.sid and s3.sid because q0.sid is ambiguous. How do I access those columns from a cross join?


Solution

  • I discovered the answer thanks to the help of my Professor. You have to brutally rename every column name using With statements. So it would be along the lines of:

    with e1 as (select t1.sid as t1sid, t1.bookno as t1bookno,
                       s1.sid as s1sid, s1.sname as s1sname,
                       s2.sid as s2sid, s2.sname as s2sname
                from buys t1 cross join student s1 cross join student s2),
         e2 as (select t1.sid as t1.sid, etc as etc) 
    

    It's just a brutal method of renaming every column you need, but it works. Sorry I didn't specify in the question that what I was trying to do is a direct translation to relational algebra in SQL. Thank you for anyone that tried to help!