Get all rows from table and join some rows fromanother table by condition

989 views mysql
-1

I have five tables, but two is main. Table itemy is table where we can found all items. And table obchody_parovani, which is table where we can found items connected with some shop.

Table Itemy

ID | nazev | cesta | druh | kvalita 
1  | nazev | cesta | 1    | 1
2  | nazev | cesta | 3    | 2
3  | nazev | cesta | 1    | 1
4  | nazev | cesta | 5    | 3

Table obchody_parovani

ID | obchod | item
1  | 1      | 2
2  | 3      | 3
3  | 1      | 1

I need recognize if item is in the shop or not. If it is, then I need ID of shop, and if is not, I need NULL.

Result which I want for obchod from obchody_parovani is id 1

ID | nazev | cesta | druh_n | kvalita_n | obchod
1  | nazev | cesta | 1      | 1         | NULL
2  | nazev | cesta | 3      | 2         | 1
3  | nazev | cesta | 1      | 1         | NULL
4  | nazev | cesta | 5      | 3         | 1

nazev, cesta, druh_n, kvalita_n is not important, i need column obchod. If table contains row with obchod = 1, than in result will be 1, or if is not, then will be NULL

My current SQL is:

SELECT i.id, i.nazev, i.cesta, d.druh AS druh_n, k.nazev AS kvalita_n, par.obchod FROM itemy AS i LEFT JOIN itemy_druhy AS d ON d.id = i.druh LEFT JOIN itemy_kvalita AS k ON k.id = i.kvalita LEFT JOIN itemy_rozdeleni AS r ON r.id = i.rozdeleni LEFT JOIN obchody_parovani AS par ON par.item = i.id WHERE par.obchod = 1

answered question

What SQL you tried so far? What is the problem?

I add SQL, sorry

My current sql now display only rows where obchod has row with the same item, I understand why and why it is wrong, but I don't know how to do SQL for result which I want

1 Answer

13

You can try to use OUTER JOIN base on Itemy table.

Schema (MySQL v5.7)

CREATE TABLE Itemy(
   ID INT,
   nazev VARCHAR(50),
  cesta VARCHAR(50), druh INT, kvalita INT
);


INSERT INTO Itemy VALUES (1,'nazev','cesta',1,1);
INSERT INTO Itemy VALUES (2,'nazev','cesta',3,2);
INSERT INTO Itemy VALUES (3,'nazev','cesta',1,1);
INSERT INTO Itemy VALUES (4,'nazev','cesta',5,3);

CREATE TABLE obchody_parovani(
   ID INT,
   obchod INT,
  item INT
);


INSERT INTO obchody_parovani VALUES (1, 1,2);
INSERT INTO obchody_parovani VALUES (2, 3,3);
INSERT INTO obchody_parovani VALUES (3, 1,1);

Query #1

SELECT i.*,op.obchod 
FROM Itemy i 
LEFT JOIN obchody_parovani op on i.druh = op.obchod 
and i.kvalita = op.item
order by i.id;

| ID  | nazev | cesta | druh | kvalita | obchod |
| --- | ----- | ----- | ---- | ------- | ------ |
| 1   | nazev | cesta | 1    | 1       | 1      |
| 2   | nazev | cesta | 3    | 2       |        |
| 3   | nazev | cesta | 1    | 1       | 1      |
| 4   | nazev | cesta | 5    | 3       |        |

View on DB Fiddle


From your tried query I think you can try to let par.obchod = 1 condition move to on clause from where

SELECT i.id, i.nazev, i.cesta, d.druh AS druh_n, k.nazev AS kvalita_n, par.obchod
 FROM itemy AS i 
 LEFT JOIN itemy_druhy AS d ON d.id = i.druh 
 LEFT JOIN itemy_kvalita AS k ON k.id = i.kvalita 
 LEFT JOIN itemy_rozdeleni AS r ON r.id = i.rozdeleni
 LEFT JOIN obchody_parovani AS par ON par.item = i.id and par.obchod = 1 

posted this

Have an answer?

JD

Please login first before posting an answer.