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:

[code=sql]

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

[/code]

[jritamar03]

Try this query:

[code=sql]

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

[/code]

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.xC ode 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.xC ode and Outlet_ID.xCode.

Regards,

Jen

Try this:

[code=sql]

SQL> SELECT outlet_table.oi d, 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_tabl e.xc,outlet_tab le.xc) > 0
5 /

OID ID XC XC
--------- --------- --- -------
999999 1 SG9 SG3,SG9

SQL>

[/code]

[jritamar03]

Try this:

[code=sql]

SQL> SELECT outlet_table.oi d, 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_tabl e.xc,outlet_tab le.xc) > 0
5 /

OID ID XC XC
--------- --------- --- -------
999999 1 SG9 SG3,SG9

SQL>

[/code]

this is great! thank you it worked! =)

[amitpatel66]

this is great! thank you it worked! =)

You are welcome :)