Problem with Oracle query

am 13.10.2006 16:33:31 von Rosen Marinov

Hi,
I have a problem with PHP and Oracle SQL query.
I have 2 tables :

Table Main:
-----------
id number
text varchar2(100)

Table Recs
-----------
id number
pos number
log varchar2(200)

I need to make query to select a list from first table(Main), where
somewhere in the second table (Recs), the log field contains some
string. The records for one ID from Main table can be many in table
Recs. ( Table Recs acts as log for every row from table Main).

Can someone help me with this query?

Thanks in advance,
Rosen

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Problem with Oracle query

am 13.10.2006 16:41:22 von Brad Bonkoski

Rosen wrote:
> Hi,
> I have a problem with PHP and Oracle SQL query.
> I have 2 tables :
>
> Table Main:
> -----------
> id number
> text varchar2(100)
>
>
> Table Recs
> -----------
> id number
> pos number
> log varchar2(200)
>
> I need to make query to select a list from first table(Main), where
> somewhere in the second table (Recs), the log field contains some
> string. The records for one ID from Main table can be many in table
> Recs. ( Table Recs acts as log for every row from table Main).
>
> Can someone help me with this query?
>
> Thanks in advance,
> Rosen
>
select m.id, r.log from main m left outer join recs r on r.id = m.id

This will output something like:
Id log
1 log item 1
1 log item 2
1 log item 3
2 another log
etc...

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Problem with Oracle query

am 13.10.2006 16:53:55 von Rosen Marinov

"Brad Bonkoski" wrote in message
news:452FA592.6020602@mediaguide.com...
> Rosen wrote:
>> Hi,
>> I have a problem with PHP and Oracle SQL query.
>> I have 2 tables :
>>
>> Table Main:
>> -----------
>> id number
>> text varchar2(100)
>>
>>
>> Table Recs
>> -----------
>> id number
>> pos number
>> log varchar2(200)
>>
>> I need to make query to select a list from first table(Main), where
>> somewhere in the second table (Recs), the log field contains some string.
>> The records for one ID from Main table can be many in table Recs. ( Table
>> Recs acts as log for every row from table Main).
>>
>> Can someone help me with this query?
>>
>> Thanks in advance,
>> Rosen
>>
> select m.id, r.log from main m left outer join recs r on r.id = m.id
>
> This will output something like:
> Id log
> 1 log item 1
> 1 log item 2
> 1 log item 3
> 2 another log
> etc...

Thanks, but I want to retrieve only one row for ID field (row from Main
table) where the string contains in second table.
Something like this:

ID Text
1 Sometext 1
2 Sometext 2
6 Sometext 3

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Problem with Oracle query

am 13.10.2006 16:59:20 von roy.a.jones

--=_alternative 005256B085257206_=
Content-Type: text/plain;
charset=us-ascii
Content-Transfer-Encoding: 7bit

Try this ...

SELECT m.id
,m.text
FROM main m
,recs r
WHERE m.id = r.id
AND r.log like '%sometext%'
ORDER BY m.id
/

Roy A. Jones

"Rick"
13-Oct-2006 10:53

To
php-db@lists.php.net
cc

Subject
Re: [PHP-DB] Problem with Oracle query

"Brad Bonkoski" wrote in message
news:452FA592.6020602@mediaguide.com...
> Rosen wrote:
>> Hi,
>> I have a problem with PHP and Oracle SQL query.
>> I have 2 tables :
>>
>> Table Main:
>> -----------
>> id number
>> text varchar2(100)
>>
>>
>> Table Recs
>> -----------
>> id number
>> pos number
>> log varchar2(200)
>>
>> I need to make query to select a list from first table(Main), where
>> somewhere in the second table (Recs), the log field contains some
string.
>> The records for one ID from Main table can be many in table Recs. (
Table
>> Recs acts as log for every row from table Main).
>>
>> Can someone help me with this query?
>>
>> Thanks in advance,
>> Rosen
>>
> select m.id, r.log from main m left outer join recs r on r.id = m.id
>
> This will output something like:
> Id log
> 1 log item 1
> 1 log item 2
> 1 log item 3
> 2 another log
> etc...

Thanks, but I want to retrieve only one row for ID field (row from Main
table) where the string contains in second table.
Something like this:

ID Text
1 Sometext 1
2 Sometext 2
6 Sometext 3

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

--=_alternative 005256B085257206_=--

Re: Problem with Oracle query

am 13.10.2006 17:09:44 von Rosen Marinov

wrote in message
news:OF4327B8B7.97B4FBCB-ON85257206.005248C5-85257206.005256 B2@gsk.com...
> Try this ...
>
> SELECT m.id
> ,m.text
> FROM main m
> ,recs r
> WHERE m.id = r.id
> AND r.log like '%sometext%'
> ORDER BY m.id
> /

Thanks, but this retrieves me so much rows as many are the appearance the
'sometext' in the Recs table

>
>
> Roy A. Jones
>
>
>
>
> "Rick"
> 13-Oct-2006 10:53
>
> To
> php-db@lists.php.net
> cc
>
> Subject
> Re: [PHP-DB] Problem with Oracle query
>
>
>
>
>
>
>
> "Brad Bonkoski" wrote in message
> news:452FA592.6020602@mediaguide.com...
>> Rosen wrote:
>>> Hi,
>>> I have a problem with PHP and Oracle SQL query.
>>> I have 2 tables :
>>>
>>> Table Main:
>>> -----------
>>> id number
>>> text varchar2(100)
>>>
>>>
>>> Table Recs
>>> -----------
>>> id number
>>> pos number
>>> log varchar2(200)
>>>
>>> I need to make query to select a list from first table(Main), where
>>> somewhere in the second table (Recs), the log field contains some
> string.
>>> The records for one ID from Main table can be many in table Recs. (
> Table
>>> Recs acts as log for every row from table Main).
>>>
>>> Can someone help me with this query?
>>>
>>> Thanks in advance,
>>> Rosen
>>>
>> select m.id, r.log from main m left outer join recs r on r.id = m.id
>>
>> This will output something like:
>> Id log
>> 1 log item 1
>> 1 log item 2
>> 1 log item 3
>> 2 another log
>> etc...
>
> Thanks, but I want to retrieve only one row for ID field (row from Main
> table) where the string contains in second table.
> Something like this:
>
> ID Text
> 1 Sometext 1
> 2 Sometext 2
> 6 Sometext 3
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Problem with Oracle query

am 13.10.2006 17:23:15 von roy.a.jones

--=_alternative 0054878985257206_=
Content-Type: text/plain;
charset=us-ascii
Content-Transfer-Encoding: 7bit

When you say so much rows I am assuming you mean 1 row from MAIN for every
row in RECS. If that is true and you just want 1 MAIN row then I would
add DISTINCT or EXISTS or IN ...

SELECT DISTINCT
m.id
,m.text
FROM main m
,recs r
WHERE m.id = r.id
AND r.log like '%sometext%'
ORDER BY m.id
/

Roy A. Jones

"Rosen"
13-Oct-2006 11:09

To
php-db@lists.php.net
cc

Subject
Re: [PHP-DB] Problem with Oracle query

wrote in message
news:OF4327B8B7.97B4FBCB-ON85257206.005248C5-85257206.005256 B2@gsk.com...
> Try this ...
>
> SELECT m.id
> ,m.text
> FROM main m
> ,recs r
> WHERE m.id = r.id
> AND r.log like '%sometext%'
> ORDER BY m.id
> /

Thanks, but this retrieves me so much rows as many are the appearance the
'sometext' in the Recs table

>
>
> Roy A. Jones
>
>
>
>
> "Rick"
> 13-Oct-2006 10:53
>
> To
> php-db@lists.php.net
> cc
>
> Subject
> Re: [PHP-DB] Problem with Oracle query
>
>
>
>
>
>
>
> "Brad Bonkoski" wrote in message
> news:452FA592.6020602@mediaguide.com...
>> Rosen wrote:
>>> Hi,
>>> I have a problem with PHP and Oracle SQL query.
>>> I have 2 tables :
>>>
>>> Table Main:
>>> -----------
>>> id number
>>> text varchar2(100)
>>>
>>>
>>> Table Recs
>>> -----------
>>> id number
>>> pos number
>>> log varchar2(200)
>>>
>>> I need to make query to select a list from first table(Main), where
>>> somewhere in the second table (Recs), the log field contains some
> string.
>>> The records for one ID from Main table can be many in table Recs. (
> Table
>>> Recs acts as log for every row from table Main).
>>>
>>> Can someone help me with this query?
>>>
>>> Thanks in advance,
>>> Rosen
>>>
>> select m.id, r.log from main m left outer join recs r on r.id = m.id
>>
>> This will output something like:
>> Id log
>> 1 log item 1
>> 1 log item 2
>> 1 log item 3
>> 2 another log
>> etc...
>
> Thanks, but I want to retrieve only one row for ID field (row from Main
> table) where the string contains in second table.
> Something like this:
>
> ID Text
> 1 Sometext 1
> 2 Sometext 2
> 6 Sometext 3
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

--=_alternative 0054878985257206_=--

Re: Problem with Oracle query

am 13.10.2006 17:32:17 von Rosen Marinov

Yes,
Thath is !
Thank you very much!

wrote in message
news:OF28B02AF2.0A8D473D-ON85257206.00545190-85257206.005487 8B@gsk.com...
> When you say so much rows I am assuming you mean 1 row from MAIN for every
> row in RECS. If that is true and you just want 1 MAIN row then I would
> add DISTINCT or EXISTS or IN ...
>
> SELECT DISTINCT
> m.id
> ,m.text
> FROM main m
> ,recs r
> WHERE m.id = r.id
> AND r.log like '%sometext%'
> ORDER BY m.id
> /
>
>
> Roy A. Jones
>

Yes,
Thath is !
Thank you very much!

>
>
>
> "Rosen"
> 13-Oct-2006 11:09
>
> To
> php-db@lists.php.net
> cc
>
> Subject
> Re: [PHP-DB] Problem with Oracle query
>
>
>
>
>
>
>
>
> wrote in message
> news:OF4327B8B7.97B4FBCB-ON85257206.005248C5-85257206.005256 B2@gsk.com...
>> Try this ...
>>
>> SELECT m.id
>> ,m.text
>> FROM main m
>> ,recs r
>> WHERE m.id = r.id
>> AND r.log like '%sometext%'
>> ORDER BY m.id
>> /
>
> Thanks, but this retrieves me so much rows as many are the appearance the
> 'sometext' in the Recs table
>
>
>
>
>
>
>>
>>
>> Roy A. Jones
>>
>>
>>
>>
>> "Rick"
>> 13-Oct-2006 10:53
>>
>> To
>> php-db@lists.php.net
>> cc
>>
>> Subject
>> Re: [PHP-DB] Problem with Oracle query
>>
>>
>>
>>
>>
>>
>>
>> "Brad Bonkoski" wrote in message
>> news:452FA592.6020602@mediaguide.com...
>>> Rosen wrote:
>>>> Hi,
>>>> I have a problem with PHP and Oracle SQL query.
>>>> I have 2 tables :
>>>>
>>>> Table Main:
>>>> -----------
>>>> id number
>>>> text varchar2(100)
>>>>
>>>>
>>>> Table Recs
>>>> -----------
>>>> id number
>>>> pos number
>>>> log varchar2(200)
>>>>
>>>> I need to make query to select a list from first table(Main), where
>>>> somewhere in the second table (Recs), the log field contains some
>> string.
>>>> The records for one ID from Main table can be many in table Recs. (
>> Table
>>>> Recs acts as log for every row from table Main).
>>>>
>>>> Can someone help me with this query?
>>>>
>>>> Thanks in advance,
>>>> Rosen
>>>>
>>> select m.id, r.log from main m left outer join recs r on r.id = m.id
>>>
>>> This will output something like:
>>> Id log
>>> 1 log item 1
>>> 1 log item 2
>>> 1 log item 3
>>> 2 another log
>>> etc...
>>
>> Thanks, but I want to retrieve only one row for ID field (row from Main
>> table) where the string contains in second table.
>> Something like this:
>>
>> ID Text
>> 1 Sometext 1
>> 2 Sometext 2
>> 6 Sometext 3
>>
>> --
>> PHP Database Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>>
>>
>>
>>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php