help with query/table structure

[poncenby]

table1 has the field pid which is the primary key and references the
field id in table2.
table2 has three fields: key,value,id.

it is a one to many relationship between table1 and table2.

i will mainly need to query on the value of the key field, for example.

select pid,value from table1,table2 where id=pid and key = 'Password';

but the results from the above query is only part of what I need. I
need to filter on the value of the key field but when it matches (for
example on 'Password') i need to return all records where the pid and
id fields match, so the resultset will contain the record with the
value 'Password' but also all other values that have the same id as the
matched 'Password' record.

hope it makes sense

poncenby

[Captain Paralytic]

Well I THINK it makes sense, but it took quite a lot of figuring out. I
don't see what your table1 gives you, as the pid value that you are
extracting will always have the same value as the id field in table2.

I think that this may be what you are after:

SELECT `t1`.`pid` , `t3`.`value`
FROM `table2` `t2`
JOIN `table2` `t3` ON `t2`.`id` = `t3`.`id` AND `t2`.`key` = 'Password'
JOIN `table1` `t1` ON `t1`.`pid` = `t2`.`id`