Posted by Sean on 11/30/06 14:19

"Brian" <not@given.com> wrote in message
news:nYAbh.676$yK2.87@newsfe7-win.ntli.net...

--- snip ---

> 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

[Back to original message]