i couldnt writethis sql query

table

pID
1
2
...
-------------
table2

ID pID field value
1 1 color red
2 1 city 4
3 1 sta true
...
------------------------

my query is

select * from table left join table2 on table.pID=table2.pID where table2.field='color' and table2.value='red'


i want to pull the red line

note : field and value has been created by users

thanks

 

Couple things.
First, I hope those are example names because 'table', 'field' and 'value' are reserved words and shouldn't actually be used as names of database objects.

Second, what's the point of a LEFT JOIN that doesn't include any of the the values in the table on the LEFT?

Third, I believe this SQL might work (failure would be because of reserved words)

SELECT table.*
FROM table2 LEFT JOIN [table] ON table2.pID = table.pID
WHERE (((table2.field)="color") AND ((table2.value)="red"));

PHP:

 

Well you have got the solution for your problem.
If face problem in creating query then you can use TOAD for mysql .

This tool will help you to write complex query in an easy manner .
i hope this will help you.