MySQL: Limit record recording based on value from another table

Question

MySQL: Limit record recording based on value from another table

Navigation

#1 by (4 votes)

1

I have a A table with a start_date field and another end_date .

I have a B table with a date field.

Is there any way to ensure that a record is only saved in B when B.date is between A.start_date and A.end_date ?

I know how to do this by inserting via PHP. But I want to know if there is anything that can be done in the Bank that guarantees this kind and integrity. I'm looking for something in the line of action of the constraints (but any solution is welcome!)

mysql

asked by anonymous 27.03.2018 / 20:21

1 answer

Algorithm to determine the prime numbers between 2 and N. What is wrong? Best Mysql Type to count accesses

score 4 · Answer 1

Yes, you can do INSERT from a SELECT where in your WHERE clause it will be determined that the date has to be between the period in tabela_a and it is enough to limit the query to 1 to enter only the registration once, even finding more occurrences, for example:

CREATE TABLE tabela_a(
    start_date DATE, 
    end_date DATE
);
CREATE TABLE tabela_b(
    campo1 varchar(255), 
    campo2 varchar(255), 
    campo3 varchar(255), 
'date' DATE
);

INSERT INTO tabela_a(
    start_date, 
    end_date) 
VALUES
    ('2018-03-01', '2018-03-30'),
    ('2018-03-01', '2018-03-29'),
    ('2018-03-01', '2018-03-05');

Here you will enter the records "value1, value2, value3, 2018-03-27" because it found in tabela_a occurrences.

INSERT INTO tabela_b(campo1, campo2, campo3, 'date')
SELECT 
    'valor1',
    'valor2',
    'valor3',
    '2018-03-27'
FROM tabela_a
WHERE '2018-03-27' between start_date and end_date
LIMIT 1;

SELECT
    *
FROM tabela_b;

Here is the query print of what was entered in tabela_b :

I think it meets your need.