|
|
Posted by Sean on 11/30/06 10:23
"Brian" <not@given.com> wrote in message
news:Jxrbh.809$I6.203@newsfe3-gui.ntli.net...
>
> "Sean" <sean.anderson@[nospam]oakleafgroup.biz> wrote in message
> news:1164716566.392107@kestrel.skynet.co.uk...
>>
>> "Brian" <not@given.com> wrote in message
>> news:tdCah.22442$0x.5146@newsfe1-win.ntli.net...
>>> Hi there
>>>
>>> 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
[Back to original message]
|