CODEWITHSUNDEEP
mysqlsql
dungphanxuan
dungphanxuan

Reputation: 615

Limit data from another table attribule

I have table Job and table Company. Each job item will be for a company. I would like to write a query that will list the jobs.

Company has a quantity column. When jobs are listed from Job, the number of jobs listed for a given company is the number in this Quantity column. Data example

For example: Company A has quantity = 2. The query data will return the top 2 jobs.

How can I do this in a sql query?

Table Job

| id | Title | Company ID   |
|:---|------:|:------------:|
| 1  | Job 1 |   1
| 2  | Job 2 |   1
| 3  | Job 3 |   1
| 4  | Job 4 |   2
| 5  | Job 5 |   2

Table Company

| id | Title     | Quantity|
|:---|----------:|:-------:|
| 1  |  Company 1|   2
| 2  |  Company 2|   2

And the result of query Select * From Job => With condition limit quantity of company.

| id | Title | Company ID |
|:---|------:|:----------:|
| 1  | Job 1 |   1
| 2  | Job 2 |   1
| 4  | Job 4 |   2
| 5  | Job 5 |   2

Upvotes: 1

Views: 430

Answers (2)

Vedran Jukic
Vedran Jukic

Reputation: 851

Create procedure and you can use variable with LIMIT

DELIMITER $$
CREATE PROCEDURE `GetJobs`()
    NO SQL
BEGIN

DECLARE cid INT;
DECLARE jobsLimit INT;

DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;

CREATE TEMPORARY TABLE JobListTemp (
     id INT,
     Title VARCHAR(255),
     CompanyID INT
   ) ENGINE=MEMORY;

SELECT COUNT(*) FROM Company INTO n;
SET i=0;
WHILE i<n DO 

  SET cId = (SELECT id FROM Company LIMIT i,1);

  SET jobsLimit = (SELECT quantity FROM Company WHERE id = cId LIMIT 1);

  INSERT INTO JobListTemp SELECT * FROM Job WHERE CompanyID = cId LIMIT jobsLimit;

  SET i = i + 1;

END WHILE;

SELECT * FROM JobListTemp;

DROP TABLE JobListTemp;

END$$
DELIMITER ;

Upvotes: 1

Mark.Zhao
Mark.Zhao

Reputation: 1

I think you can use the inner sqlquery.

For example:

select innerTab.id,innerTab.Title,innerTab.companyid 
from (select * from job order by id limit 2) innerTab 
inner join Company on innerTab.companyid=company.companyid

The colname is not same with you.

So you can change it to make sure the sql can run.

Upvotes: 0

Related Questions