Help with JOIN, please!

[Ken Chiba]

Hi,

I'm having some real problems figuring out a seemingly simple join
statement.  I've looked through some of the mailing list posts, but I can't
seem to find what I'm looking for.  Here's what I have - 2 tables:

table1 (id, stimulus_number, stimulus_type)
table 2 (id, stimulus_number, stimulus_type)

Basically, all I want to do is to determine how many matches there are,
where the stimulus_number  stimulus_type are the same in both tables, based
on a certain id.  Here's a verbose example:

table 1: contains 2 entries for id=2
  -- stimulus_number=23, stimulus_type='word'
  -- stimulus_number=34, stimulus_type='image'

table 2: contains 2 entries for id=2
  -- stimulus_number=23, stimulus_type='word'
  -- stimulus_number=34, stimulus_type='word'

The desired result, should display the following:
  -- 23, word
... since that was the only entry that was common to both tables, for id=2
... note, both stimulus_number  stimulus_type have to match in both tables
(not just one or the other).

Here's what I've tried:
  -- select distinct a.stimulus_number, a.stimlus_type
   from table_1 as a
   left join table_2 as b
   on a.stimulus_type=b.stimulus_type
 and a.stimulus_number=b.stimulus_number
   where id=2;

... this looked ok to me, but it spits out any results that have common
stimulus_numbers, OR common stimulus_types.

Any help would be greatly appreciated,

Thanks in advance,

Ken

(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

Help with simple JOIN

[Ken Chiba]

Hi,

I'm having some real problems figuring out a seemingly simple join
statement.  I've looked through some of the mailing list posts, but I can't
seem to find what I'm looking for.  Here's what I have - 2 tables:

table1 (id, stimulus_number, stimulus_type)
table 2 (id, stimulus_number, stimulus_type)

Basically, all I want to do is to determine how many matches there are,
where the stimulus_number  stimulus_type are the same in both tables, based
on a certain id.  Here's a verbose example:

table 1: contains 2 entries for id=2
  -- stimulus_number=23, stimulus_type='word'
  -- stimulus_number=34, stimulus_type='image'

table 2: contains 2 entries for id=2
  -- stimulus_number=23, stimulus_type='word'
  -- stimulus_number=34, stimulus_type='word'

The desired result, should display the following:
  -- 23, word
... since that was the only entry that was common to both tables, for id=2
... note, both stimulus_number  stimulus_type have to match in both tables
(not just one or the other).

Here's what I've tried:
  -- select distinct a.stimulus_number, a.stimlus_type
   from table_1 as a
   left join table_2 as b
   on a.stimulus_type=b.stimulus_type
 and a.stimulus_number=b.stimulus_number
   where id=2;

... this looked ok to me, but it spits out any results that have common
stimulus_numbers, OR common stimulus_types.

Any help would be greatly appreciated,

Thanks in advance,

Ken

(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