Re: SQL Help
MATHER wrote:
>
> I have two tables, table a is a list of People, and table B is a
> descriptor field (i.e. Brown hair, Blue eyes, tall, skinny). Each
> description in Table B is a separate record. How can I get a report of
> all people who are not skinny.
>
> When I try
> select * from> table A, Table B
> where tableb.desc <> "skinny"
>
> I still get guy1 Brown hair, guy1 Blue eyes, guy1 tall. Any help is
> appreciated!!!
>
> Mike
Hi Mike !
The reason why your SELECT does'nt work is that you don't join the two
tables. So, the result is the whole cartesian product.
If one person only has one attribute the solution could look like this:
table_A (pers_id,person,attrib_id)
table_B (attrib_id,desc)
SELECT a.person
,b.desc
FROM table_A a
,table_B b
WHERE b.desc = 'skinny'
AND b.attrib_id = a.attrib_id
If one person can have more than one attribute (I think this is always true)
you need a third table, which contains all valid combinations of attributes
for the persons.
table_A (pers_id,person)
table_B (attrib_id,desc)
table_A_B (pers_id,attrib_id)
SELECT a.person
,b.desc
FROM table_A a
,table_B b
,table_A_B c
WHERE b.desc = 'skinny'
AND b.attrib_id = c.attrib_id
AND c.pers_id = a.pers_id
ORDER BY a.pers_id <--- as an option
Hope this helps!
Cheers,
Dirk
---------------------------------------------------
Dirk Schmidtpott S I E M E N S
EMail: schmd@dbm212.pdb.sni.de =============
Phone: 0049-5251-8-14922 N I X D O R F
Fax : 0049-5251-8-27905
The above statements and opinions are my own and do
not necessarily represent those of my employer.
---------------------------------------------------