Monday, February 20, 2012

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;
}
}

2 comments:

  1. Try

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

    ReplyDelete
  2. 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
    )

    ReplyDelete