[thelist] SQL question

David Kaufman david at gigawatt.com
Thu Aug 22 16:03:01 CDT 2002


Lauri Vain <lauri_lists at tharapita.com> queried thusly:
>
> FIRST TABLE:
> --[comp_id] [source] [country] [whatever] [foobar]--
> '1','Google','Neverneverland','Heh','blah'
>
> SECOND TABLE:
> --[comp_id] [www_id] [content]--
> '1','4','hohohohoo1'
> '4','3','hohohohoo2'
> '1','9','hohohohoo3'
> '2','2','hohohohoo4'
> '1','2','hohohohoo5'
>
> Now, I need to write a query, which would check whether there are rows
> WWW_ID = 4 AND WWW_ID = 2 in the second table, where COMP_ID = 1.

sounds to me like you need an outer join:

select
  count(test_first.comp_id)
from
  test_first
    left join test_second as second_A
      on test_first.comp_id = 1 and second_A.www_id = 4
    left join test_second as second_B
      on test_first.comp_id = 1 and second_B.www_id = 2
where
  test_first.comp_id=1

which yields:
+---------------------------+
| count(test_first.comp_id) |
+---------------------------+
|                         2 |
+---------------------------+

using mysql> select version();
+-------------+
| version()   |
+-------------+
| 3.23.49-log |
+-------------+

and the following actual tables and test data:

create table test_first (
  comp_id int not null auto_increment,
  source varchar(50),
  country varchar(50),
  primary key (comp_id)
);

insert into test_first values (1, 'Google','Neverneverland');

create table test_second (
  comp_id int not null,
  www_id int not null,
  content varchar(50),
  primary key (comp_id, www_id)
);

insert into
  test_second
values
  (1,4,'hohohohoo1'),
  (4,3,'hohohohoo2'),
  (1,9,'hohohohoo3'),
  (2,2,'hohohohoo4'),
  (1,2,'hohohohoo5')
;


> ...In real life that very query has some 5-6 tables and tons of other
> conditions, the entire queryline being a mile long.

real life gets that way sometimes, doesn't it?

HTH,

-dave





More information about the thelist mailing list