Many-to-many question

[jgabbai]

Hi,

I have a many to many x-ref table where column a and b have numbers:

1 | 2
2 | 2
4 | 5
7 | 8
9 | 2
10| 8

I would like to do a search where I look for a=1, and a=2 to return b=2
as it is the same value in a. I.E. Search for a=9 and a=7 will be
negative, but a=7 and a=10 will return b=8 (as it is the same).

Not sure I explained well, so ask if it is unclear.

Many thanks.

[hoonew]

jg*****@gmail.c om wrote:

Hi,

I have a many to many x-ref table where column a and b have numbers:

1 | 2
2 | 2
4 | 5
7 | 8
9 | 2
10| 8

I would like to do a search where I look for a=1, and a=2 to return b=2
as it is the same value in a. I.E. Search for a=9 and a=7 will be
negative, but a=7 and a=10 will return b=8 (as it is the same).

Not sure I explained well, so ask if it is unclear.

Many thanks.

Try:

SELECT IF(MIN(b)=MAX(b ),b,-1) FROM xreftable WHERE a IN(value1, value2,
....);

[jgabbai]

Hi,

Thanks for the suggestion. I tried it, but it will only work if the
relationship between a and b is exclusive. In other words, it should
also work for:

1 | 2
2 | 2
1 | 5
1 | 8
2 | 5
10| 8

where I enter a=1, a=2 and still get b=2 (even though a=1=b=5 etc).
Sorry for not being clearer.

[Malcolm Dew-Jones]

jg*****@gmail.c om wrote:
: Hi,

: Thanks for the suggestion. I tried it, but it will only work if the
: relationship between a and b is exclusive. In other words, it should
: also work for:

: 1 | 2
: 2 | 2
: 1 | 5
: 1 | 8
: 2 | 5
: 10| 8

: where I enter a=1, a=2 and still get b=2 (even though a=1=b=5 etc).
: Sorry for not being clearer.
Does your version of mysql have an INTERSECT (sql) operator? Mine doesn't
but it's getting a bit old now, so maybe yours does.

If so you could do something like this
select b from xref_table where a=1
interesect
select b from xref_table where a=2

that would return the set of b's that have the same a's. If you wanted to
use that as part of a boolean test then you could use it in a count and
look for a count >= 0, so something like

select count(*)
from
(
... the above intersect expression ...
)

but presumably as part of something more complicated.

$0.10

--

This space not for rent.