Re: A little problem with SELECT

2003-01-17 Thread Roger Baklund 
* Blaster
[...]
 main company table
 id (int) | company name (varchar) | phone (varchar) | .. more fields that
 doesn't really matter

 employee table (company.id = employee.cid)
 id (int) | cid (int) | name (varchar) | age (int) | email (varchar)

 Now, I want to make a search which can search for 2 names and show me if
 there are any company that
 has these names? I tried

 SELECT a.* FROM company a, employee b WHERE a.id=b.cid AND
 b.name='joe' OR
 b.name='bill';

Because you want to check two different names, you must join the employee
table twice:

SELECT a.*
  FROM company a, employee b1, employee b2
  WHERE
 a.id=b1.cid AND
 a.id=b2.cid AND
 b1.name='joe' AND
 b2.name='bill';

--
Roger
sql


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: A little problem with SELECT

2003-01-17 Thread Marcos Henke 
SELECT a.* FROM company a, employee b
WHERE a.id=b.cid AND
(b.name='joe' OR
b.name='bill');

_
Marcos Henke
_

- Original Message -
From: Roger Baklund [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: Blaster [EMAIL PROTECTED]
Sent: Friday, January 17, 2003 7:51 AM
Subject: Re: A little problem with SELECT


 * Blaster
 [...]
  main company table
  id (int) | company name (varchar) | phone (varchar) | .. more fields
that
  doesn't really matter
 
  employee table (company.id = employee.cid)
  id (int) | cid (int) | name (varchar) | age (int) | email (varchar)
 
  Now, I want to make a search which can search for 2 names and show me if
  there are any company that
  has these names? I tried
 
  SELECT a.* FROM company a, employee b WHERE a.id=b.cid AND
  b.name='joe' OR
  b.name='bill';

 Because you want to check two different names, you must join the employee
 table twice:

 SELECT a.*
   FROM company a, employee b1, employee b2
   WHERE
  a.id=b1.cid AND
  a.id=b2.cid AND
  b1.name='joe' AND
  b2.name='bill';

 --
 Roger
 sql


 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
[EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re:Re: Re:Re: A little problem with SELECT

2003-01-17 Thread nossareh 
Marcos Henke Wrote:

 SELECT a.* FROM company a, employee b
 WHERE a.id=b.cid AND
 (b.name='joe' OR
 b.name='bill');

This will pull out all companies which have a Joe or a Bill.  We want only the
 company in which Joe and Bill are working.  

A working solution was offered by Adolfo Bello earlier with a (maybe) typo. here's the 
edited version:

Select a.* from company a, employee b, employee c where
a.id = b.cid and
a.id = c.cid and
b.name = 'Joe' and
c.name = 'Bill;

Thanks
Nasser.

sql, smallint






-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

A little problem with SELECT

2003-01-16 Thread Blaster 
Hello, I have a really tough problem here, I can't think of a way to make 
this work

Imagine 2 tables, the first one is the main table where I keep listings 
of companies and general info about them.
In the second table, I keep 1 row for each employee and an field which 
points to the company (using an ID)

main company table
id (int) | company name (varchar) | phone (varchar) | .. more fields that 
doesn't really matter

employee table (company.id = employee.cid)
id (int) | cid (int) | name (varchar) | age (int) | email (varchar)

Now, I want to make a search which can search for 2 names and show me if 
there are any company that
has these names? I tried

SELECT a.* FROM company a, employee b WHERE a.id=b.cid AND b.name='joe' OR 
b.name='bill';

however, this would return any companies that has ONLY one Bill or one Joe 
.. I only want companies that
have BOTH. It also returns one row with the company per name it found, so 
you can imagine I got confused
when it found 2000 rows when my list of companies is 50 rows =) Basically, 
what I'd like to do is find the
company who has both bill and joe

I tried:
SELECT a.* FROM company a, employee b WHERE a.id=b.cid AND b.name='joe' AND 
b.name='bill';

But I realized this is all wrong cause the same cell cannot be both Joe and 
Bill =)

mysql query smallint


-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: A little problem with SELECT

2003-01-16 Thread Adolfo Bello 

 SELECT a.* FROM company a, employee b WHERE a.id=b.cid AND b.name='joe' OR 
 b.name='bill';
 however, this would return any companies that has ONLY one Bill or one Joe 
 .. I only want companies that
 have BOTH. It also returns one row with the company per name it found, so 
 you can imagine I got confused
 when it found 2000 rows when my list of companies is 50 rows =) Basically, 
SELECT DISTINCT . will give you just one row per company found.
 
 what I'd like to do is find the
 company who has both bill and joe
 
 I tried:
 SELECT a.* FROM company a, employee b WHERE a.id=b.cid AND b.name='joe' AND 
 b.name='bill';
Try:
SELECT DISTINCT a.* FROM company a, employee b, employee c WHERE
a.id=b.cid AND a.id=c.cid WHERE b.name='joe' AND c.name='bill'
 
 But I realized this is all wrong cause the same cell cannot be both Joe and 
 Bill =)
 
 mysql query smallint
HTH

__ 
   / \\  @  ____@   Adolfo Bello [EMAIL PROTECTED]
  /  // // /\  / \\  // \  //   Bello Ingenieria S.A, Presidente
 /  \\ // / \\/  // //  / //cel: +58 416 609-6213
/___ / _/\__\\//__/ // fax: +58 212 952-6797
  www.bisapi.com  //pager: www.tun-tun.com (# 609-6213)


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php