Posted by Brian on 11/30/06 13:24

>>>> I am trying to write the follwoing statement in the best way possible
>>>> in a PHP page. I think i need to use a join somewhere but i always
>>>> get confused with them, also trying to work out if it can be done
>>>> without using a loop from the first search
>>>>
>>>> I have 3 tables,
>>>> T1
>>>> T2
>>>> T3
>>>>
>>>> T1 and T2 have the same structure but just called different names
>>>>
>>>> User inputs a "search"
>>>>
>>>> SELECT `ACol` FROM `T1` WHERE `T1ACol` = "search" < this may bring
>>>> back more than 1 result
>>>>
>>>> Now
>>>>
>>>> SELECT `FieldA`, `FieldB`, `FieldC` (< Fields A,B,C from both T2 and
>>>> T3)
>>>> FROM `T2`,`T3`
>>>> WHERE T2ACol = the above results
>>>> OR
>>>> T3ACol = the above results
>>>>
>>>> Thanks
>>>>
>>>> Brian
>>>>
>>>>
>>>
>>> Try:
>>>
>>> SELECT *
>>> FROM T2, T3
>>> WHERE T2ACol IN (SELECT ACol FROM T1 WHERE T1ACol = 'search')
>>> OR T2ACol IN (SELECT ACol FROM T1 WHERE T1ACol = 'search')
>>>
>>>
>> Hi Sean
>>
>> Thanks for your reply, I have had a play and I get an error, below is a
>> cut down version
>> of the statement, it seems to complain about having a SELECT in the
>> brackets after
>> the IN, but the SELECT in the brackets works fine on it's own?
>>
>> SELECT *
>> FROM MyTable
>> WHERE prod_code IN (SELECT prod_code FROM productslist WHERE bin_location
>> = "XH256")
>>
>> Any ideas why?
>>
>> Brian
>>
>
> I am right to assume that the following returns you a recordset?
> SELECT prod_code FROM productslist WHERE bin_location = "XH256"
>
> Could you then take the first 'prod_code' from that recordset and try the
> following; of course, replacing <prod_code> with the value from your
> recordset (above)
> SELECT * FROM MyTable WHERE prod_code = "<prod_code>"
>
> Does that still work?
>
> In your PHP page, do you have the SQL statement in ONE line:
> $mysql = "SELECT...... WHERE xxx IN (SELECT ....)";
>
> Are the field types the same for MyTable.prod_code and for
> productlist.prod_code?
>
> Sean
>
>

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

[Back to original message]