Comma delimited

[jritamar03]

Hello there,

I am new in using SQL 2000. Please help me to find out how to create a select query or function to return the expected result below.

Table 1: User_Table

UserID | xName | xCode
---------------------------------------------
1 | XYZ | SG3,SG9
2 | ABC | KR12,KR13

Table 2: Outlet_Table

Outlet_ID | xCode
-------------------------------------------------------
987654 | KR12
77777 | SG9


Create a query that will display the expected result below:

Outlet_ID | xCode | UserID
----------------------------------------------------------------
987654 | KR12 | 2
77777 | SG9 | 1


Please help.

Kindest regard,
jen

[amitpatel66]

Hello there,

I am new in using SQL 2000. I need to find out how to create a select query or function from the below.

Table 1: User_Table

UserID xName xCode
------------------------------------------------
1 XYZ SG3,SG9
2 ABC KR12,KR13

Table 2: Outlet_Table

Outlet_ID xCode
---------------------------------
987654 KR12
77777 SG9


Create a query that will display the expected result below:

Outlet_ID xCode UserID
----------------------------------------------------------------
987654 KR12 2
77777 SG9 1

Please help.

Kindest regard,
jen

Try this query:

 
SELECT ot.outlet_id,ot.xcode,ut.user_id FROM
outlet_table ot, user_table ut
WHERE ut.xcode LIKE '%ot.xcode%'
 
 

[jritamar03]

Try this query:

 
SELECT ot.outlet_id,ot.xcode,ut.user_id FROM
outlet_table ot, user_table ut
WHERE ut.xcode LIKE '%ot.xcode%'
 
 

Thank you for a very quick reply.
Using Line 3. WHERE ut.xcode LIKE '%ot.xcode%' did not return any result.
I tried to add a bracket WHERE ut.xcode LIKE '%[ot.xcode]%' but the result ID is not correct. :(

[jritamar03]

Hello there,

I am new in using SQL 2000. Please help me to find out how to create a select query or function to return the expected result below.

Table 1: User_Table

UserID | xName | xCode
---------------------------------------------
1 | XYZ | SG3,SG9
2 | ABC | KR12,KR13

Table 2: Outlet_Table

Outlet_ID | xCode
-------------------------------------------------------
987654 | KR12
77777 | SG9


Create a query that will display the expected result below:

Outlet_ID | xCode | UserID----------------------------------------------------------------
987654 | KR12 | 2
77777 | SG9 | 1


Please help.

Kindest regard,
jen

Hello,

Please help me solve this comma delimited case.. the table i am querying has no primary key or even common field except for the Outlet_Table.xCode and Outlet_ID.xCode.

Regards,

Jen

[amitpatel66]

Hello,

Please help me solve this comma delimited case.. the table i am querying has no primary key or even common field except for the Outlet_Table.xCode and Outlet_ID.xCode.

Regards,

Jen

Try this:

 
SQL> SELECT outlet_table.oid, user_table.id, outlet_table.xc,user_table.xc FROM
  2  (select 1 AS id, 'XYZ' AS xname, 'SG3,SG9' AS xc FROM DUAL) user_table,
  3  (SELECT 999999 AS oid, 'SG9' AS xc FROM DUAL) outlet_table
  4  WHERE INSTR(user_table.xc,outlet_table.xc) > 0
  5  /
 
      OID        ID XC  XC
--------- --------- --- -------
   999999         1 SG9 SG3,SG9
 
SQL> 
 
 

[jritamar03]

Try this:

 
SQL> SELECT outlet_table.oid, user_table.id, outlet_table.xc,user_table.xc FROM
  2  (select 1 AS id, 'XYZ' AS xname, 'SG3,SG9' AS xc FROM DUAL) user_table,
  3  (SELECT 999999 AS oid, 'SG9' AS xc FROM DUAL) outlet_table
  4  WHERE INSTR(user_table.xc,outlet_table.xc) > 0
  5  /
 
      OID        ID XC  XC
--------- --------- --- -------
   999999         1 SG9 SG3,SG9
 
SQL> 
 
 

this is great! thank you it worked! =)

[amitpatel66]

this is great! thank you it worked! =)

You are welcome :)