Re: Combine Queries with LIKE Clause

Sam wrote:

> DA Morgan <damorgan_at_exesolutions.com> wrote in message news:<3E298ACE.DC64C844_at_exesolutions.com>...
> > Sam wrote:
> >
> > > I have two queries:
> > >
> > > select deptname from table1 (return 3 rows)
> > >
> > > select saleman from table2 where dept_desc LIKE '%table1.deptname%' (repeat 3 times)
> > >
> > > How to combine them together?
> >
> > Likely the problem is that you have created a Cartesian join. But without seeing your
> > SQL it is impossible to know.
> >
> > Daniel Morgan
>
> Thank you Daniel! Here are the queries and their results
> 1.
> select deptname from table1 (return 3 rows)
>
> returns three deptname
>
> management
> sale
> engineer
>
> 2.
>
> select saleman from table2 where dept_desc LIKE '%management%'
>
> returns
>
> Amy
> Tony
>
> note: table2 is directly converted from mainframe dept_desc field looks like
> "0056894_xxxx_management_line_435....."
>
> select saleman from table2 where dept_desc LIKE '%sale%'
>
> returns
>
> Sreve
> Tony
> ...
>
> select saleman from table2 where dept_desc LIKE '%engineer%'
>
> returns
>
> David
> ...

Your queries aren't returning the same result set. Try this and I think it will become clear what is happening:

SELECT 'table1' TNAME, deptname, dept_desc, salesman FROM table1
UNION ALL
SELECT 'table2' TNAME, deptname, dept_desc, salesman FROM table2;

Then looking at the output walk through your query logic and see what is happening. One this is that is you had a dept_desc like 'sales management' you would get the same record multiple times. I wonder if you understand what your LIKE %...% is actually asking for.

Daniel Morgan Received on Sat Jan 18 2003 - 13:43:28 CST