Dotaz: vnoreny SELECT a podmienka

[tina]

Ahoj, mam nasledujucu qwery:

SELECT  policy_table.user_group_name,
	policy_table.object_spec_desc, 
	policy_table.action_spec_desc, 
	policy_table.object_specification, 
	service_type_action.service_type_name, 
	service_type_action.action_name

FROM policy_table

LEFT JOIN service_type_action

ON policy_table.action_specification = service_type_action.service_action_id;

z ktorej vypis je nasledujuci:

 user_group_name | object_spec_desc | action_spec_desc | object_specification | service_type_name | action_name
-----------------+------------------+------------------+----------------------+-------------------+-------------
 superUserGroup  | user             | serviceAction    | bob                  | cas               | grantAll
 analytici       | userGroup        | serviceAction    | analytici            | cas               | grantAll
 vedci           | userGroup        | serviceAction    | vedci                | cas               | grantAll
 vedci           | trustAnchor      | serviceAction    | KarolovaTA           | cas               | grantAll
 superUserGroup  | user             | serviceAction    | karol                | cas               | grantAll
 superUserGroup  | object           | serviceAction    | 4                    | cas               | grantAll
 superUserGroup  | objectGroup      | serviceAction    | data                 | cas               | grantAll
 superUserGroup  | serviceType      | serviceAction    | directory            | cas               | grantAll
 superUserGroup  | object           | serviceAction    | 1                    | cas               | superuser
 analytici       | objectGroup      | serviceAction    | data                 | directory         | citanie
 superUserGroup  | object           | serviceAction    | 1                    | directory         | citanie

Ak object_specification je cislo tak referuje na object_id tabulky object_table kde v stlpci object_name
je meno objektu ktore potrebujem zobrazit vo vypise miesto tohoto cisla. Ak v qwery miesto policy_table.object_specification
dam vnoreny select: (SELECT object_table.object_name FROM object_table WHERE object_table.object_id = policy_table.object_specification LIMIT 1)
tak sa mi mena tych objektov vo vypise zobrazia, ale ostatne riadky v stlpci object_specification su prazdne. 

 

Cela qwery po pridani vnoreneho selectu:

SELECT 	policy_table.user_group_name, 
	policy_table.object_spec_desc,
	policy_table.action_spec_desc,
	(SELECT object_table.object_name FROM object_table WHERE object_table.object_id = policy_table.object_specification LIMIT 1),
 	service_type_action.service_type_name,
	service_type_action.action_name 

FROM policy_table 

LEFT JOIN service_type_action 

ON policy_table.action_specification = service_type_action.service_action_id;

 

user_group_name | object_spec_desc | action_spec_desc |         ?column?         | service_type_name | action_name
-----------------+------------------+------------------+--------------------------+-------------------+-------------
superUserGroup  | user             | serviceAction    |                          | cas               | grantAll
analytici       | userGroup        | serviceAction    |                          | cas               | grantAll
vedci           | userGroup        | serviceAction    |                          | cas               | grantAll
vedci           | trustAnchor      | serviceAction    |                          | cas               | grantAll
superUserGroup  | user             | serviceAction    |                          | cas               | grantAll
superUserGroup  | object           | serviceAction    | ftp://ulozisko.yweb.sk/* | cas               | grantAll
superUserGroup  | objectGroup      | serviceAction    |                          | cas               | grantAll
superUserGroup  | serviceType      | serviceAction    |                          | cas               | grantAll
superUserGroup  | object           | serviceAction    | casServer                | cas               | superuser
analytici       | objectGroup      | serviceAction    |                          | directory         | citanie
superUserGroup  | object           | serviceAction    | casServer                | directory         | citanie

 

Nevie mi niekto poradit ako formulovat qwery aby sa zobrazili mena aj mena objektov sucasne?

 

Odpovědi

[ZAH]

Seš si jist, že vždy vrací

SELECT object_table.object_name FROM object_table WHERE object_table.object_id = policy_table.object_specification LIMIT 1

dotaz záznam.

[tina]

No to nie lebo su tam iba dva zaznamy cize na object_id = 1 a 4. Tu je tabulka object_table:

 object_id |       object_name        | namespace_nickname
-----------+--------------------------+--------------------
         1 | casServer                | casNamespace
         4 | ftp://ulozisko.yweb.sk/* | FTPDirectoryTree

[mess]

Tohle by nešlo?

SELECT 	policy_table.user_group_name, 
	policy_table.object_spec_desc,
	policy_table.action_spec_desc,
	object_table.object_name,
	(SELECT object_table.object_name FROM object_table WHERE (object_table.object_id = policy_table.object_specification or object_table.object_name = policy_table.object_specification) LIMIT 1),
 	service_type_action.service_type_name,
	service_type_action.action_name 

FROM policy_table 

LEFT JOIN service_type_action 

ON policy_table.action_specification = service_type_action.service_action_id;

Případně tam dej druhý join s object_table (takže ve výsledku bude join 3 tabulek).

[tina]

TO nie vyhoti my to chybu:

CHYBA:  chýbajúci záznam v klauzuli FROM pre tabulku "object_table"

[pSipi]

Pouzi Case

Case

When  (SELECT object_table.object_name FROM object_table WHERE object_table.object_id = policy_table.object_specification LIMIT 1) = Null Then policy_table.object_specification

Else (SELECT object_table.object_name FROM object_table WHERE object_table.object_id = policy_table.object_specification LIMIT 1)

End Case

 

Neviem ci je spravny syntax. Ja pisem hlavne v TSQL

[pSipi]

Popripade urob ten join a vysledok urob z obidvoch stlpcov cez case alebo isnull (ma mysql isnull?)

[tina]

Super, takto som to upravil a ide to:

SELECT 	policy_table.user_group_name,
	policy_table.object_spec_desc, 
	policy_table.action_spec_desc, 
	CASE WHEN (SELECT object_table.object_name FROM object_table WHERE object_table.object_id = policy_table.object_specification LIMIT 1) IS NULL THEN 
		policy_table.object_specification 
	ELSE (SELECT object_table.object_name FROM object_table WHERE object_table.object_id = policy_table.object_specification LIMIT 1) 
	END, 
	service_type_action.service_type_name, 
	service_type_action.action_name 

FROM policy_table 

LEFT JOIN service_type_action 

ON policy_table.action_specification = service_type_action.service_action_id;

Dakujem Vsetkym za pomoc, hlavne pSipi.

 

[xkucf03]

1. Špatně navržený datový model (v jednom sloupci se míchají jak textové hodnoty, tak číselné odkazy někam jinam) → jestli do toho modelu můžeš zasáhnout, tak to udělej (v tom sloupečku bude např. jen číslo, cizí klíč vedoucí do object_table).
2. Tenhle CASE funguje, ale ten vnořený SELECT se bude volan u každého řádku a pokud výsledek nebude NULL, zavolá se ještě jednou, aby se ta hodnota zjistila (THEN).
3. Místo podmínky můžeš použít funkci NVL(), v MySQL se jmenuje IFNULL(sloupeček1, sloupeček2) – pokud je sloupeček1 NULL, nahradí se hodnotou sloupeček2. Místo sloupeček2 může být i vnořený dotaz:

   IFNULL(sloupeček1, (SELECT 'ahoj')
   

   Jenže tady narážíš na prasáckost toho modelu – alespoň malé zlepšení by bylo, mít tam místo jednoho sloupečku dva – pokud je ten jeden nulový (NULL), dohledáš si pomocí ifnull druhého sloupečku a vnořeného dotazu tu správnou hodnotu (pak se ten vnořený select pustí, jen když je potřeba).