CODEWITHSUNDEEP
phpmysqlsql
Ajay Patel
Ajay Patel

Reputation: 5418

Subquery returns more than 1 row

All

I have two table enter image description here

1st table is : wp_frm_item_metas 2nd table is : wp_frm_items

Now i want to meta_value based on wp_frm_items table fields value.

I fired this sql. it returns me Subquery returns more than 1 row

SELECT meta_value 
FROM wp_frm_item_metas 
WHERE (item_id=(
  SELECT id 
  FROM wp_frm_items 
  WHERE form_id ='9' && user_id='1') 
&& field_id=128)

I tried this solution 

SELECT meta_value 
FROM wp_frm_item_metas 
WHERE (item_id=(
  SELECT count(*) as c,id 
  FROM wp_frm_items 
  WHERE form_id ='9' && user_id='1') 
&& field_id=128 && c > 1) 
ORDER BY c DESC

It returns this error Operand should contain 1 column(s)

My code is 

foreach($fp_id_c as $kid=>$id)
{
    if (!$id or ($logged_in && !$user_ID)) return;
    $id = (int)$id;

    //echo $logged_in.'-'.(int)$user_ID;
    if ($logged_in){
        echo $ff_id_c[$kid];
        global $wpdb;
        echo "SELECT meta_value FROM wp_frm_item_metas WHERE (item_id=(SELECT count(*) as c,id FROM wp_frm_items WHERE form_id ='$kid' && user_id='". (int)$user_ID ."') && ORDER BY c DESC && field_id=$ff_id_c[$kid])";
        $result_c= $wpdb->get_var("SELECT meta_value FROM wp_frm_item_metas WHERE (item_id IN (SELECT count(*) as c,id FROM wp_frm_items WHERE form_id ='$kid' && user_id='". (int)$user_ID ."') && field_id=$ff_id_c[$kid]) ORDER BY c DESC");
        echo $result_c; 
    }
}

Upvotes: 2

Views: 4072

Answers (3)

Mosty Mostacho
Mosty Mostacho

Reputation: 43494

I see you're joining two tables. Why don't you do it the usual way? :)

select meta_value from wp_frm_item_metas metas
join wp_frm_items items on
items.id = metas.item_id
where items.form_id = 9 and items.user_id = 1 and metas.field_id = 128

Upvotes: 1

Shakti Singh
Shakti Singh

Reputation: 86416

Try the IN clause

SELECT meta_value FROM  wp_frm_item_metas 
WHERE (item_id IN (
                     SELECT id FROM 
                    wp_frm_items WHERE 
                    form_id ='9' && user_id='1'
                   ) 
         && field_id=128
        )

Upvotes: 5

Sunil Kumar B M
Sunil Kumar B M

Reputation: 2795

Try

WHERE item_id IN (<your sub query>) instead of WHERE item_id=

Upvotes: 1

Related Questions