Admin do you have MongoDB group too?

Someone please help me out 🙁

So I found a solution like this:

WITH total as (SELECT SUM(Amount) as total FROM “table”)
SELECT Amount *100/total.total AS Percent FROM “table”,total;

Is there a better solution?

Hello everybody needs some help in fetching comma-separated data by combining Two tables resume and vacancy. wants to show results by matching same work location and same job_category and interest.

SELECT vacancy.job_category, vacancy.job_location, resume.interest, resume.work_location FROM vacancy, resume
WHERE vacancy.job_category LIKE ‘%resume.interest%’

this is the query I used but no result is showing need help to build the query

So interest IN job_category and job_location IN work_location?

Is that what you want?

Let me try once

IN won’t work

As its not a column – values are separated by comma

Ya that is the problem I want to fix

Single data is getting fetched

However, you can extract the individual values using SUBSTR in a subquery and then compare data

But not with , separated

Hmm, let me try..

I was mistaken

Regexp cannot be used since sql doesn’t allow values from another column to be matched in its regex expression

The FIND_IN_SET() fx has to be used – I think I know how to do this

Will post the solution in 3 – 4 hours

Yaa that I tried yesterday but didn’t help therefore switch to other methods

This one is quite good, but ISIN a derived table introduced with WITH keyword is not necessary here, you could just have it like a simple uncorrelated subquery.

Emmm….
It seems you have join condition missing in your quiery. You have two tables, so there must be some join condition in it.

Also, never use non-ANSI join syntax, use JOIN explicitly.

@Neche_se_topper

Here’s your query:

SELECT `resume`.`Person_ID`, `vacancy`.* FROM `vacancy`
INNER JOIN `resume` ON
(FIND_IN_SET(REPLACE(`vacancy`.`job_location`,’ ‘,”), REPLACE(`resume`.`work_location`,’ ‘,”)) > 0 AND
FIND_IN_SET(REPLACE(`vacancy`.`job_category`,’ ‘,”), REPLACE(`resume`.`interest`,’ ‘,”)) > 0);

resume table

vacancy table

Query result

If you want, you can add a LOWER() fx to REPLACE()

But FIND_IN_SET() fx is case-insensitive

Such join will not work in production, it is O(N * M), N,M – sizes of the tables.

I know 🙁

But I couldn’t find a faster way; I mean, a join has to happen but optimizing this query is out of scope for me xD

Either way, at least the query will produce results now

You must redesign the DB

Not necessarily a better solution this is. But temporary tables are useful too

Well, I did suggest it yesterday – but maybe its for a project so he didn’t heed my advice

Yes, WITH is only available since MYSQL 8. It could have been done with normal query too

I think storing the data as json would be better

Go easy on me, I’m just learning SQL

I’m a MongoDB gal xD

Just saying ))

Well, then you can teach me a lot about MongoDB too. Trying to do self study and was hoping to experiment with replication from MYSQL to Mongo. As lazy I am I never could. 😁

Admin do you have MongoDB group too?

Mongo is a lot more flexible – but the more I learn about RDBMS, the more I get impressed

Well at least I assume you have such setup in your organization so you learn new things. I am stuck with MYSQL and MariaDB as they only use this. And once I was out of University I had little motivation to read. Although I read lot about other things not tech.

Mongo gets a pretty bad rep in the industry because of its ‘non-ACID compliance’ when its mostly untrue

Mongo’s advantage lies in its flexible schema design – so updation of one document is inherently ACID compliant

But Mongo now supports multi doc transactions, which comes as a performance tradeoff.

Hardcore companies will not move away from RDBMS since there’s been a lot of research in it and it would seem as a sort of an unnecessary risk(now that DBs support JSON, why would they need to even..)

As for me – I will prefer RDBMS for structured data anyday