Hi,

I have a simple problem, but I spent more than 2 days thinking about,
and I was not able to build a SQL that resolves my problem...

I have 3 tables: 1 called COMPANY that has a company_code(int) and
company_name(char) columns. Another 1 called TYPE that has also two
columns: type_code(int) and type_name(char).

The last table is COMPANY_TYPE and has FK from the other two:
company_code and type_code.

The COMPANY table has 3 registers: 1 = 'Company A', 2 = 'Company B'
and 3 = 'Company C';

The TYPE table has 3 registers: 1 = 'Client', 2 = 'Reseller' and 3 =
'Prospect';

Finally, the last one holds the relation between the other two, and it
has the following: 1,1 ; 1,2 ; 1,3 ; 2,2 ; 3,1 ; 3,2

How do I search the COMPANY_TYPE table in order to find wich company
has some set of types?

For instance: I want to find a company that is "client" AND "reseller"

If I do something like "SELECT company_code FROM COMPANY_TYPE WHERE
type_code = 1 AND type_code = 2" SQL Server returns 0 rows...

I'm not a very skilled in SQL. In fact I'm a java programmer and I am
trying to build a report.

I my mind it should be very easy to do that, but I dont know if I am
blind or I am really stupid...

I dont know if I could make myself understood. I am from Brazil and
english isn't easy for me...

Thanks in advance for any help.

Bob

PS.: I posted this message here because I understand that is a simple
SQL problem, not an MS SQL Server problem... By the way, I am using
the 2000 version of it.

bobrivers (AT) pobox (DOT) com (Bob Rivers) wrote in message news:<aa669e9b.0307051943.806aa25 (AT) posting (DOT) google.com>...
That is correct, there are no rows where type_code = 1 and type_code =
2. I'll try to explain:

What you want is all companies that are both clients and resellers.
You can transform that to, All companies c where there exists a row in
company_type where type_code = 1 and company = c and where there
exists a row that has type_code = 2 and company c. I.e.

select c.* from company c
where
exists (
select 1 from COMPANY_TYPE
where company_code = c.company_code and company_type = 1
) AND exists (
select 1 from COMPANY_TYPE
where company_code = c.company_code and company_type = 2
)

Another solution would have been

select c.* from company c, company_type ct
where c.company_code = ct.company_code and ct.company_type = 1
intersect
select c.* from company c, company_type ct
where c.company_code = ct.company_code and ct.company_type = 2

In most cases I would go for the first solution

HTH
/Lennart

Lennart Jonsson wrote:
and a third solution is to realize there is no need to separate the use of the
same table into two subqueries (as the previous answers do). So you can try:

select c.*
from company c, company_type ct, company_type bt
where c.company_code = ct.company_code and ct.company_type = 1
and c.company_code = bt.company_code and bt.company_type = 2 ;

Keep your options open.

--
Ed Prochak
running http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost