How to have more than one value referring to another table?

Question

How to have more than one value referring to another table?

Navigation

#1 by (1 votes)

-1

Good people, I come with a doubt that has been chasing me for a long time.

Let's go to what I have:

I have the table X but values from the table X I get in the Y table . How to store more than one value in the Y table for X table and the Y table can also reference the same values for other tables.

With this I have a problem that I need a lot of help.

What do I think? Create another table to support these values for example:

I have the X table and the Y table . I create the table Z where it will store everything that X needs in the table Y so if I have a G table which also needs a Y table item, I will store all the G items in the Z table

I need to know if this thought is correct or if there is another way to do it.

Personally, thank you in advance. Thank you for helping me.

php mysql sql

asked by anonymous 31.03.2016 / 04:32

1 answer

Validating form for the same page only when clicking "Call to undefined method mysqli_result :: fetchAll ()" in PDO [closed]

score 1 · Accepted Answer

From my understanding of your question, you want to pull the data from two tables in one query, is that it? If it is, in the blog of Thiago Belem has a great article that was where I learned, and < a href="https://sqlfromhell.wordpress.com/2009/09/15/working-with-join/"> here has a little more on the subject.

Example: You have two tables in the database with some data entered

CREATE TABLE 'categorias' (
    'id' INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    'nome' VARCHAR( 255 ) NOT NULL
) ENGINE = MYISAM;

INSERT INTO 'categorias' VALUES
(1, 'Camisetas'),
(2, 'Canecas');

CREATE TABLE 'produtos' (
    'id' INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    'categoria_id' INT NOT NULL ,
    'nome' VARCHAR( 255 ) NOT NULL ,
    'preco' DECIMAL( 10,2 ) NOT NULL
) ENGINE = MYISAM;

INSERT INTO 'produtos' VALUES
(1, 1, 'Camiseta Social', 15.00),
(2, 1, 'Camiseta Regata', 11.99),
(3, 2, 'Caneca Grande', 12.00);

And you want a return like this:

| id | categoria_id | nome            | preco | categoria |
| 2  | 1            | Camiseta Regata | 11.99 | Camisetas |
| 1  | 1            | Camiseta Social | 15.00 | Camisetas |
| 3  | 2            | Caneca Grande   | 12.00 | Canecas   |

You should mount a QUERY like this:

SELECT p.*, c.'nome' AS categoria FROM 'produtos' AS p
INNER JOIN 'categorias' AS c ON p.'categoria_id' = c.'id'
ORDER BY p.'nome' ASC

Where INNER JOIN will merge the categories into the search for the products table. That I believe, be your doubt.

(Source: Thiago Belem)