dealing with more than 1 table

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

Re: dealing with more than 1 table

"Brian" 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')

Re: dealing with more than 1 table

"Sean" wrote in message
news:1164716566.392107@kestrel.skynet.co.uk...
>
> "Brian" 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

Re: dealing with more than 1 table

"Brian" wrote in message
news:Jxrbh.809$I6.203@newsfe3-gui.ntli.net...
>
> "Sean" wrote in message
> news:1164716566.392107@kestrel.skynet.co.uk...
>>
>> "Brian" 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 with the value from your
recordset (above)
SELECT * FROM MyTable WHERE 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

Re: dealing with more than 1 table

>>>> 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 with the value from your
> recordset (above)
> SELECT * FROM MyTable WHERE 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

Re: dealing with more than 1 table

"Brian" 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

Re: dealing with more than 1 table

>> 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