Intersection between columns of separate tables in Mysql

Asked

Viewed 107 times

0

By the book:

1 - Summarize the problem:
I’m Unable to Achieve an Intersection between columns of different tables

2 - Include details about your goal
I am building a PHP BINGO and need to realize an Intersection between two different tables in my Mysql database the column with the numbers drawn and the column with the sequence inserted in the cards.

3 - Describe current and expected results
===== EDITED EXCERPT
Let’s take a step-by-step example of what INNER JOIN did and what I expected it to do:
First we have to create two tables (follows simplified structure already with inserted values):

--
-- Estrutura da tabela `tabela_1`
--

CREATE TABLE IF NOT EXISTS `tabela_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `coluna_a` varchar(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


INSERT INTO `tabela_1` (`id`, `coluna_a`) VALUES
(1, '1, 2, 3');

--
-- Estrutura da tabela `tabela_2`
--

CREATE TABLE IF NOT EXISTS `tabela_2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `coluna_b` varchar(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;


INSERT INTO `tabela_2` (`id`, `coluna_b`) VALUES
(1, '1, 2, 3'),
(2, '1, 2'),
(3, '1,'),
(4, '2,'),
(5, '1, 2, 3');

In this structure we will execute the command:

--
-- Comando
-- 
SELECT coluna_a FROM tabela_2
INNER JOIN
tabela_1 ON tabela_2.coluna_b=tabela_1.coluna_a

Ball Show!!! Works!!!
Returns the values

1, 2, 3

Now let’s go to the example that does not work and returns zero query (0)
First we create the same structure, only now with different values:

--
-- Estrutura da tabela `tabela_1`
--

CREATE TABLE IF NOT EXISTS `tabela_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `coluna_a` varchar(500) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;


INSERT INTO `tabela_1` (`id`, `coluna_a`) VALUES
(1, '12, 9, 6, 5, 7, 3, 15, 1, 11,');

-- --------------------------------------------------------

--
-- Estrutura da tabela `tabela_2`
--

CREATE TABLE IF NOT EXISTS `tabela_2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `coluna_b` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Extraindo dados da tabela `tabela_2`
--

INSERT INTO `tabela_2` (`id`, `coluna_b`) VALUES
(1, '1, 2, 3, 9, 12,'),
(2, '1, 3, 6, 7, 10,'),
(3, '3, 5, 9, 11, 15,'),
(4, '5, 6, 8, 12, 15,'),
(5, '1, 7, 11, 12, 15,');

What I expected to return, with my deepest humility and as far as my intelligence can take me, was the following return:

3, 5, 9, 11, 15 => id=3
1, 7, 11, 12, 15 => id=5
  • INNER JOIN does exactly what you have described, the rows in which the values are common in the two columns used as the join criterion. I don’t know what you mean by "dynamic records".

  • INNER JOIN only intersects if the columna_a has the value of columna_b. About the column_a having dynamic values, I mean that the value of the column_a is always receiving new values.

  • I would be grateful to know how you would make this idea work. : )

  • When you execute a database command the result of this command takes into account the position of the database at the time of execution. Any subsequent database modification will only be captured in a new execution of the command. DBMS does not predict the future.

  • I don’t think I made myself clear. It is not a crystal ball that I need, but crossings of data entered in the database. (1, 5, 7, 8, 10 <=> 2, 5, 10 ==> 5, 10) Simple. Unfortunately the INTERSECT does not work in Mysql this would be very simple to solve.

  • But what was the result obtained by the SQL command you posted and why didn’t it meet you? INNER JOIN does exactly the intersection.

  • Friend, I really appreciate your effort in trying to understand me, taking this into account, I edited my question with the practical and simplified example of how INNER JOIN worked for me and what it did not answer me. Thank you for your time.

  • There is a conceptual error in the definition of your database, its coluna_a and coluna_b fields do not contain multiple values but a single value, in this case a single string as for example: '1, 3, 6, 7, 10'. A junction is defined as an operation between tables and the criterion between fields. My suggestion is that you normalize the tables of your database if the fields coluna_a and coluna_b actually have to record the different values, for example: 1, 3, 6, 7 and 10 one on each line.

  • I see your point. My point is that the column_b has numbers of cards of a bingo, it is the sequence that has each card, ie sequence of the letter B = 1, 6, 8, 15, 10. and the coluna_a, are the stones drawn, I wonder when the sequence of the coluna_a, will appear in counas_b, regardless of the line or sequence, the coluna_a will be receiving data, that is to say varying.

Show 4 more comments
No answers

Browser other questions tagged

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