Nested select statement in mysql -my code doesn't work

Question

here is my query

SELECT con_serial,column2,column3
 FROM
 (SELECT con_serial,column2,column3
 FROM big_table 
 WHERE ISNULL(contact1, '')+'#'+ISNULL(contact2, '')+'#'+ISNULL(contact3, '')+'#'+ISNULL(contact4, '')+'#'+ISNULL(contact5, '')
 LIKE '%' + '".$conserial."' + '%') AS a
 WHERE con_serial
IN('".$contact1."','".$contact2."','".$contact3."','".$contact4."','".$contact5."')

at the inner select i wish to get the rows which have this value $conserial in one of their 5 columns(contact1...contact5)

and the outer select to choose the rows from it that their column con_serial is one of the variables ($contact1...$contact5)

can anybody see what's wrong here?

This is a total mess! First you're mixing PHP (likely) with SQL while not being in the PHP context. Then it's not clear if `$con_serial` OR the five `$contact1` etc are the selection criterium. And finally what mean `ISNULL` here, and what about `#` and `+`? — cFreed, Dec 13 '16 at 20:59
thank you for trying to help. belive me if this is mess my whole project is just disaster. yeah i'm using php and query is going to be run in php contex actually i'm kind of new to mysql and i'm using the isnull() to replace the null values with ` ' ' ` i want to chek if any of those five column in any row contain an spesific value`$con_serial` and if so get them and ofCourse use this result in outer select is there a better achive for this?? @cFreed — khoshrang, Dec 13 '16 at 21:40
It's not easy to understand what you need exactly: following your description I see kind of contradiction. It seems that: 1) the PHP `$conserial` value is searched in any of the five columns `contactN`; 2) the column `con_serial` value should match any of the five PHP `$contactX`. This is likely not really what you want. Please elaborate, and show us: 1) the `big_table` structure; 2) the list of the PHP variables used as criterions. — cFreed, Dec 13 '16 at 23:29
Tnx man i changed the structure should have done this earlier — khoshrang, Dec 13 '16 at 23:46

score 0 · Answer 1 · answered Dec 13 '16 at 23:44

i guess the sum part in where clause is nut allowed any way i've solved this with using two IN like this

SELECT con_serial,,column2,column3
 FROM(SELECT con_serial,column2,column3
 FROM
  big_table
 WHERE '".$conserial."' IN(contact1,contact2,contact3,contact4,contact5)) a  
  WHERE con_serial IN('".$contact1."','".$contact2."','".$contact3."','".$contact4."','".$contact5."'

this was wat i wanted Tnx any way ;)

cFreed · Accepted Answer · 2016-12-14T01:15:16.323

Despite your new formulation, it remains very much unclear.
Nevertheless I'll try to give you an answer, based on what I can guess...

First here is how I'd reformulate your need:

you have some values in PHP variables: one $conserial and five $contact# where # is 1-5
the table structure contains at least these columns: con_serial, column2, column3, and five contact# where # is 1-5
you want to select rows where both (here is the most strange part of your need):
- at least one of the contact# columns matches the PHP $conserial value
- the con_serial column matches at least one of the PHP $contact# values

That said, note that you don't need to have two nested SELECT: you only want each row to satisfy two conditions, so they can be ANDed in the WHERE clause.

Based on that, your query should be:

$query = "
  SELECT con_serial, column2, column3
  FROM big_table
  WHERE con_serial IN ('$contact1', '$contact2', '$contact3', '$contact4', '$contact5')
  AND '$con_serial' IN (contact1, contact2, contact3, contact4, contact5)
";

Nested select statement in mysql -my code doesn't work

2 Answers2