E SA <[EMAIL PROTECTED]> wrote on 02/08/2005 06:40:40 PM:
>
> All,
>
> I have done some reading and research; however, I
> seem to be at a loss...
>
> And this time, I am not sure how to ask Google...
>
> Here is the problem:
>
> Table A: id INT
> value varchar (10)
>
> Table B: id INT
> value varchar (10)
>
> Table C: id INT
> A INT
> B INT
> D INT
>
> Table D: id INT
> ...
> other_values
>
> Table C is in order to "normalize" values for A and B
>
> So, I want to be able to do a query to C that returns
> the id values of D in order to cross reference that
> table later.
>
> Since table C only knows the numeric values of the
> string, I need to be able to do so as part of the
> query by comparing C.a with A.id; however, I also
> need to compare with the value string...
>
> Now, I can do that with one table (A, for example):
>
> mysql> select C.D from C, A
> -> where C.a = A.id AND A.value='berry';
>
> However, I am not sure how to add the next condition:
>
> -> where C.b = B.id AND B.value='fruit';
>
> That would allow me to obtain the values on C where
> A.value = berry and B.value = fruit; however using
> the numeric values of A.id and B.id
>
> There most be a simple solution...
>
> To add to the problem, I am using MySQL 4.0.15, so the
> multiple select would not work...
>
> Any help would be appreciated as I have little hair
> left!!!
>
> Beforehand, thank you for the help!
>
If you lurk here long, you would find out that I am NOT a fan of the
comma-separated JOIN construction. I prefer to EXPLICITLY declare my JOINS
so that is how I will respond to your request. It's not invalid to do it
the other way, I just feel it leaves the user open to more opportunities
for mistakes. Also, the explicit form is the only way to declare the outer
joins (LEFT JOIN or RIGHT JOIN). You can't do that with the comma-list
method.
Since you say that you only want rows from C that match the conditions you
impose on the A and B tables, you should use INNER JOINs to lookup your d
values from C (it's possible with the another type of join to achieve the
same results but it won't be as efficient).
SELECT C.d
FROM C
INNER JOIN A
ON A.ID = C.a
INNER JOIN B
ON B.ID = C.b
WHERE A.value='berry'
AND B.value = 'fruit'
However, you could save yourself a step and get the records straight from
D (unless you need that list of C.d values for other purposes)
SELECT D.ID, D.somefield, D.someotherfield,...
FROM D
INNER JOIN C
ON D.ID = C.d
INNER JOIN A
on C.a = A.ID
INNER JOIN B
ON B.ID = C.b
WHERE A.value='berry'
AND B.value='fruit'
There are lots of tutorials out there that teach the basics of JOINing
tables. Hopefully this will get you started!!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
