Posted by Brian on 12/06/06 15:30

>> Hi Sean
>>
>> At the moment I am running this through PHPmyAdmin I have not written the
>> PHP code
>>
>> The first statement does return a record set of 2 records
>> SELECT prod_code FROM productslist WHERE bin_location = "XH256"
>>
>> prod_code
>> H1719
>> 337/341
>>
>> So i have tried
>>
>> SELECT *
>> FROM MyTable
>> WHERE prod_code IN ("H1719","337/341")
>>
>> Works find, i have also tried
>>
>> SELECT *
>> FROM t1,t2
>> WHERE t1.prod_code IN ("H1719", "337/341")
>> OR t2.prod_code IN ("H1719", "337/341")
>>
>> Also works fine, but if i replace the ("H1719", "337/341")
>> with (SELECT prod_code FROM productslist WHERE bin_location = "XH256")
>> it falls over.
>>
>> I could get round this by running a statment to build up the string and
>> put the
>> string in but i don't get why it does not like the SELECT in the IN
>>
>> SELECT *
>> FROM t1,t2
>> WHERE t1.prod_code IN ($MyString)
>> OR t2.prod_code IN ($MyString)
>>
>> All the prod_code are a VARCHAR so they are the same
>>
>> Brian
>>
>>
>
> Try
> SELECT * FROM t1
> WHERE prod_code IN (SELECT prod_code FROM productslist)
>
> And
>
> SELECT * FROM t1
> WHERE prod_code IN (SELECT prod_code FROM productslist WHERE bin_location
> = "XH256")
>
> - Can you ensure that each of the above (in turn) are entered as one line
> of SQL.
>
> - What's the exact error you are being returned when you execute the SQL
>
> - Could you confirm the version of SQL that you're running.
>
> To test this I have created two tables on my computer ("tasks" and
> "day_types"). "tasks" contains two columns ('task' and 'task_day') and
> "days" contains two columns ('day_name' and 'day_type'). The data is
> simple:
>
> "TASKS"
> 'task_day' 'task'
> Mon Work Hard
> Tue Work Hard
> Wed Work Hard
> Thur Work Hard
> Fri Surf Internet
> Sat Football
> Sun Church
>
> "DAY_TYPES"
> 'day' 'day_type'
> Mon Weekday
> Tue Weekday
> Wed Weekday
> Thur Weekday
> Fri Weekday
> Sat Weekend
> Sun Weekend
>
> Two queries:
> (1) select * from where task_day in (select day from day_types where
> day_type = "Weekday")
> Mon Work Hard
> Tue Work Hard
> Wed Work Hard
> Thur Work Hard
> Fri Surf Internet
>
> (2) select * from where task_day not in (select day from day_types where
> day_type = "Weekday")
> Sat Football
> Sun Church
>
> Not a lot of help, but it does prove that the principal works.
>
> Just thinking about this, perhaps you could go about it another way....
>
> SELECT *
> FROM productslist PL
> LEFT JOIN table1 T1 on T1.prod_code = PL.prod_code
> LEFT JOIN table2 T2 on T2.prod_code = PL.prod_code
> WHERE PL.bin_location = "XH256"
>
>
>
>
> Sean
>
>
>
>
>

Hi Sean

Sorry for the delay, we have just had a new server installed and I have been
sorting out
the fallout from the swap over.

Anyway just started looking at this again, and guess what, it's now working
!! Guest the
old server had a problem

Thanks for your help

Brian

[Back to original message]