Getting more than 1 rows in subquery

anjanesh · #1 (**permalink**) 06-25-06, 05:38

Code:

SELECT tbl1.`fld1`, (SELECT `fld` FROM `tbl2`) FROM `tbl1` tbl1

says #1242 - Subquery returns more than 1 row

But I want these more-than-one-row values.

How do I get that ?

Thanks

r937 · #2 (**permalink**) 06-25-06, 08:41

the only way to do what you want is with a cross join:

Code:

select tbl1.fld1
     , tbl2.fld
  from tbl1
cross
  join tbl2

this will give you every value of fld1 in tbl1 with every value of fld in tbl2, which is what your original query would have done if it were legal