[mysql] Podwojny klucz obcy Opcje

[cornholio666]

Witam,

mam takie tabele




Chciałbym pobrać nazwe producenta z tabeli stop i start dla produktu nr 1

To zapytanie wyciaga nazwę producenta z tabeli start

[SQL] pobierz, plaintext 
SELECT pr.nazwa FROM producent AS pr, produkt  AS p,start WHERE p.produkt_id = 1 AND start.produkt_id = p.produkt_id AND pr.producent_id = start.producent_id
[SQL] pobierz, plaintext 

a jak pobrać jeszcze z tabeli stop?

[mike]

~cornholio666 powiedz co to za schemat.
Bo zamiast myśleć nad zapytaniem może powinieneś zmienić schemat bazy. Bo z tego co widzę to jakiś dziwoląg jest.

[cornholio666]

Powiedzmy ze jest jeden produkt który może mieć dwoch producentów. Przychodzi klient i mowi ze chce zmienic producenta swojego produktu.

start - producent jakiego mial wczesniej

stop - producent na jakiego chce zmienic

W produkcie ma byc zawarte jaki był wcześniej producent i zmiana na nowego (to jest na sztywno w kazdym produkcie)

[mike]

Ja bym zrobił tak:

Tabela product:

[SQL] pobierz, plaintext 
CREATE TABLE `test`.`product` (
`id` INT( 4 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 255 ) NOT NULL ,
`description` TEXT NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
[SQL] pobierz, plaintext 

Tabela producer:

[SQL] pobierz, plaintext 
CREATE TABLE `test`.`producer` (
`id` INT( 4 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 255 ) NOT NULL ,
`description` TEXT NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
[SQL] pobierz, plaintext 

Tabela łącząca powyższe, producer_product:

[SQL] pobierz, plaintext 
CREATE TABLE `test`.`producer_product` (
`producer_id` INT( 4 ) NOT NULL ,
`product_id` INT( 4 ) NOT NULL ,
`deleted_at` DATETIME NULL DEFAULT NULL ,
PRIMARY KEY ( `producer_id` , `product_id` )
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci 
 
ALTER TABLE `producer_product` ADD FOREIGN KEY ( `producer_id` ) REFERENCES `test`.`producer` ( `id` );
 
ALTER TABLE `producer_product` ADD FOREIGN KEY ( `product_id` ) REFERENCES `test`.`product` ( `id` );
[SQL] pobierz, plaintext 

I teraz masz tak:

1. Dodajemy produkty:

[SQL] pobierz, plaintext 
INSERT INTO `test`.`product` ( `id` , `name` , `description` ) 
VALUES ( 1 , 'Omega', 'Fajna bryka' ), ( 2 , 'Punto', 'Lorem ipsum' ), ( 3 , 'Palio weekend', 'Bla bla' );
[SQL] pobierz, plaintext 

2. dodajemy producentów:

[SQL] pobierz, plaintext 
INSERT INTO `test`.`producer` ( `id` , `name` , `description` )
VALUES ( 1 , 'Opel', 'Marka I' ), ( 2 , 'Fiat', 'Bla bla' );
[SQL] pobierz, plaintext 

3. Łączymy:

[SQL] pobierz, plaintext 
INSERT INTO `test`.`producer_product` ( `producer_id` , `product_id` , `deleted_at` )
VALUES ( '1', '1', NULL ), ( '2', '2', NULL ), ( '2', '3', NULL );
[SQL] pobierz, plaintext 

Masz teraz wszystko jak na dłoni.
Możesz łatwo łącząc tabele wyciągać producentów po produkcie, produkty po producentach, ...
Pamiętając że aktywny producent to ten, który ma `deleted_at` == NULL

Teraz chcesz zmienić producenta dla Omegi (id=1 w tabeli product) z Opla (id=1 w tabeli producer) na Fiata (id=2 w tabeli producer):

[SQL] pobierz, plaintext 
UPDATE `producer_product` SET `deleted_at` = NOW( ) WHERE `producer_product`.`producer_id` =1 AND `producer_product`.`product_id` =1 
 
INSERT INTO `test`.`producer_product` ( `producer_id` , `product_id` , `deleted_at` ) VALUES ( '1', '2', NULL );
[SQL] pobierz, plaintext 

Takim oto zabiegiem masz:
1. Zmienionego producenta;
2. Zachowanego ostatniego, starego producenta i łatwy dostęp do niego (wyszukujesz te rekordy, które sa usunięte, sortujesz po czasie usunięcia malejąco i dajesz limit na 1);
3. BONUS: Cała historię zamian.

[cornholio666]

mike mi chodzi o cos takiego


PRODUKT
id| producent_1 | producent_2 | kryterium_1 | kryterium_2 cena przy takiej zmianie
------------------------------------------------------------------------------------------------
1| opel | nissan | xxx | sss | 5000 zł
2| audi | bmw | qqq | ddd | 1 0000 zł


Musze mieć w jednym wierszu producent_1 (to co mam teraz) i producent_2 (na co chce zmienić)

Przychodzi koleś do mnie mówi: "chce zmienić opla na nissana" . Szukam producent_1 = opel producent_2 = nissan mowie mu "to bedzie kosztować 5000 zł."

Przy twojej strukturze nie jestem w stanie stwierdzić z jakiego produktu chce przyejsc na jaki. Tutaj jeden słownik producent musi być przypisany 2 razy do tabeli PRODUKT.

[Cienki1980]

A takie coś nie zadziała

[SQL] pobierz, plaintext 
SELECT pr_start.nazwa AS start_nazwa, pr_stop.nazwa AS stop_nazwa,p.pole FROM produkt p
	INNER JOIN start ON p.produkt_id=start.produkt_id
	INNER JOIN producent pr_start ON start.producent_id=pr_start.producent_id
	INNER JOIN stop ON p.produkt_id=stop.produkt_id
	INNER JOIN producent pr_stop ON stop.producent_id=pr_stop.producent.id
[SQL] pobierz, plaintext 

[cornholio666]

Cienki1980 dzięki o to chodziło

Wkradła ci się literówka:

[SQL] pobierz, plaintext 
INNER JOIN producent pr_stop ON stop.producent_id=pr_stop.producent_id
[SQL] pobierz, plaintext