Make Join in table that has two ID in the same column

Asked

Viewed 159 times

-1

The scenario is as follows: I have my service order tables and my technicians scheduling tables for each service order. The problem is: In my OS table I have, for example, the protocols "123" and "456". In my scheduling table, in my column "ID_OS", I have the following value: "B:123 T:456", this in the same field, because it refers to a scheduling for two products (broadband and telephone).

How is it possible for me to leave my OS table, which will have a row for ID 123 and a row for ID 456 and perform a Join that considers these cases of double products that will appear, in the same column, in the same cell, the values "B:123 T:456" ?

The ideal scenario is for me to bring, in the result, a line for OS 123, with the technician’s information, and a line for OS 456, with the same technical information, but I need these two lines.

Follow SQL Fiddle containing an example of the current state of the database: http://sqlfiddle.com/#! 4/38269b/3

sql join

  • Should OS 789 have only one line as well? Would your result then have three lines with the link data? Can you put an example of how you would like it to be the result of this query.

  • It is certainly not a good practice for you to have multivariate attributes in your table. I wouldn’t say it’s impossible for you to do Join but it would be much simpler if the attributes of your table were atomic.

  • Unfortunately I can’t change the table, I have no power over it and the system already creates that way.

  • @Danielmendes yes, OS 780 has to have only one line, I put it in the example because it is not all that happen this problem of multivariate attributes. At the end I need three lines: One for each OS containing some data from the technical table.

  • This would be the expected result: https://i.stack.Imgur.com/T7mhk.png

  • @Exact Danielmendes, the OS 123 and 456 with extra information from the table of technicians, which only has one line for these two OS

Show 1 more comment

2 answers

0

I saw there that you are using Oracle... not PL/SQL manjo, but if it was in SQL Server I would do something like this:

SELECT * FROM TECH_TABLE AS A CROSS APPLY (SELECT REPLACE(REPLACE(value,’D:','),'F:','') AS ITEM FROM SPLIT_STRING(A.SO_ID,' ')) B; INNER JOIN SO_TABLE AS C ON B.ITEM = C.SO_ID

I would use cross apply with split to break and treat the code and then make a Join with the other table. If error in Inner Join after cross, invert them.

0


How the data is present in SO_TABLE without formatting, a possible solution is to try to do the join of these tables using the like of SQL.

If the data present in TECH_TABLE.SO_ID is contained in SO_TABLE.SO_ID, he will be considered.

See an example of how to do this:

SELECT *
  FROM SO_TABLE SO
  JOIN TECH_TABLE TE
    ON TE.SO_ID LIKE '%' || SO.SO_ID ||'%';

It is at your discretion and rule of business to make a left join, right join etc, how to find best.

If the like is too wide, you can work with the formatting features present in the table TECH_TABLE, using the letter and colon ('B:'):

SELECT *
  FROM SO_TABLE SO
  JOIN TECH_TABLE TE
    ON ( TE.SO_ID = SO.SO_ID
      OR TE.SO_ID LIKE '%B:' || SO.SO_ID || '%'
      OR TE.SO_ID LIKE '%T:' || SO.SO_ID || '%' );

With this I bring the ID contained in the letters or the ID equality.

If there are many letters, it is possible to filter only the two points:

SELECT *
  FROM SO_TABLE SO
  JOIN TECH_TABLE TE
    ON ( TE.SO_ID = SO.SO_ID
      OR TE.SO_ID LIKE '%:' || SO.SO_ID || '%' );

Equal ID or ':' and ID contained.

See online: http://sqlfiddle.com/#! 4/38269b/37

  • I believe that is correct, from what I see here. I had not thought to use the like in Join. I’ll just check to make sure you don’t get OS wrong and everything, because of the like, and also need to see if it won’t get too slow and heavy the query. But thanks, you helped me a lot.

Browser other questions tagged sql join

You are not signed in. Login or sign up in order to post.