asked on
SQL and Java question.
I need help with how to do this.
I have a table with colums, and one column has system names delimited by a comma.
My challenge is to query each record in this table (the table is very small).
Each record in the table has an ID number, name, and system fields.
The system field can have many entries, delimited by commas.
But I also need to query another table that also has a system field, and I need to match these two table's system fields up and display those records that match, based upon the system field.
Can this be done in one query?
Does this sound like a strange request?
Can someone help with a working example?
Cheers
I have a table with colums, and one column has system names delimited by a comma.
My challenge is to query each record in this table (the table is very small).
Each record in the table has an ID number, name, and system fields.
The system field can have many entries, delimited by commas.
But I also need to query another table that also has a system field, and I need to match these two table's system fields up and display those records that match, based upon the system field.
Can this be done in one query?
Does this sound like a strange request?
Can someone help with a working example?
Cheers
JSPJava EESQL
Last Comment
Muhammad Khan
try:
select * from table1 ,table2 where table1.systemfield=table2.
select * from table1 ,table2 where table1.systemfield=table2.
ASKER
Kind of, but no.
Remember, that my system field in table1 can have multiple system names that are comma delimited. In table2 the system field will only have one system name.
I think more has to be done to iterate through each system name in table1, right?
Remember, that my system field in table1 can have multiple system names that are comma delimited. In table2 the system field will only have one system name.
I think more has to be done to iterate through each system name in table1, right?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Interesting!
Am I understanding correctly, that the double bars "||" you have inside the percent signs will cause the query to iterate through and compare each system name in table2 with the single system name in table1?
Am I understanding correctly, that the double bars "||" you have inside the percent signs will cause the query to iterate through and compare each system name in table2 with the single system name in table1?
ASKER
The parameters for this question has changed.
This answer will work for this issue.
I will open another question to address the new requirements.
Thanks
This answer will work for this issue.
I will open another question to address the new requirements.
Thanks
No.. this double pipe sign is a concatenation operator for string in PLSQL and in oracle if you want to pattern match the string.. you use % sign in conjunction with LIKE operator ..
so for example
'abc' LIKE '%b%' will return true...
and
'100,01,03' LIKE '%,01,%' will return true.. that's doing the trick in your question.. no need to iterate... just pattern match..
so for example
'abc' LIKE '%b%' will return true...
and
'100,01,03' LIKE '%,01,%' will return true.. that's doing the trick in your question.. no need to iterate... just pattern match..
where t1.system_field=t2.system_
is that what you want?