MPetrovic MPetrovic - 13 days ago 7
SQL Question

How to join results from multiple sql queries

I wannt to display the required columns that is defines in SELECT, once the one condition is executed and prints result of the condition, i want in new specified column from SELECT to display from another where clause, and so on, first somehow, i need to solve with this two queries, after that plan is to mix with about 10 queires. Thanks P.s one of the problems that, everything of results is displayed in column i_di1.naziv

SELECT bolnickiracun.id,
bolnickiracun.id_dijagnoza1, i_di1.naziv naziv_dijagnoza1
FROM bolnickiracun
LEFT JOIN i_dijagnoze_marko i_di1 ON bolnickiracun.id_dijagnoza1=i_di1.id
WHERE bolnickiracun.id_dijagnoza1!="" and i_di1.naziv is null
UNION ALL
SELECT bolnickiracun.id,
bolnickiracun.id_dijagnoza2, i_di2.naziv naziv_dijagnoza2
FROM bolnickiracun
LEFT JOIN i_dijagnoze_marko i_di2 ON bolnickiracun.id_dijagnoza2=i_di2.id
WHERE bolnickiracun.id_dijagnoza2!="" and i_di2.naziv is null


I want result to look like this:

id id_dijagnoza1 naziv_dijagnoza1 id_dijagnoza2 naziv_dijagnoza2
1 some data null
2 some data null
3 some data null
4 some data null


point is to find naziv_dijagnoza1 or naziv_dijagnoza2 where is null, ie, where foreign key is showing not existing primary key, or someone import bad foreign key that not exist as a primary key.

Answer

This example for 3 SELECT's (third one predicted by deduction based on pattern that can be seen) should help understand what you need to do with whole query for 10 SELECT's. You need to fill with NULL's every column after SELECT clause you dont'need in every SELECT.

SELECT bolnickiracun.id, bolnickiracun.id_dijagnoza1, i_di1.naziv naziv_dijagnoza1, NULL, NULL, NULL, NULL -- and so on
FROM     bolnickiracun
LEFT JOIN i_dijagnoze_marko i_di1 ON bolnickiracun.id_dijagnoza1=i_di1.id
WHERE  bolnickiracun.id_dijagnoza1!="" and i_di1.naziv is null

UNION ALL

SELECT bolnickiracun.id, NULL, NULL, bolnickiracun.id_dijagnoza2, i_di2.naziv naziv_dijagnoza2, NULL, NULL -- and so on
FROM     bolnickiracun
LEFT JOIN i_dijagnoze_marko i_di2 ON bolnickiracun.id_dijagnoza2=i_di2.id
WHERE  bolnickiracun.id_dijagnoza2!="" and i_di2.naziv is null

UNION ALL

SELECT bolnickiracun.id, NULL, NULL, NULL, NULL, bolnickiracun.id_dijagnoza3, i_di3.naziv naziv_dijagnoza3 -- and so on
FROM     bolnickiracun
LEFT JOIN i_dijagnoze_marko i_di3 ON bolnickiracun.id_dijagnoza3=i_di3.id
WHERE  bolnickiracun.id_dijagnoza3!="" and i_di3.naziv is null

The number of columns in every SELECT should be the same and as you can see, for 3 SELECTs it's 7 columns (2 per table joined x 3 SELECTs + 1 first column with ID). For 10 SELECTs it will be 21 columns in every SELECT clause (1 for id, 2 like in every table and the rest filled with NULLs). Not pretty solution but probably only with database structure like that.

So for 10 SELECT's the frist and the last should look like this:

    SELECT bolnickiracun.id, bolnickiracun.id_dijagnoza1, i_di1.naziv naziv_dijagnoza1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
FROM     bolnickiracun
LEFT JOIN i_dijagnoze_marko i_di1 ON bolnickiracun.id_dijagnoza1=i_di1.id
WHERE  bolnickiracun.id_dijagnoza1!="" and i_di1.naziv is null

UNION ALL
...
UNION ALL

SELECT bolnickiracun.id, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, bolnickiracun.id_dijagnoza10, i_di10.naziv naziv_dijagnoza10
FROM     bolnickiracun
LEFT JOIN i_dijagnoze_marko i_di10 ON bolnickiracun.id_dijagnoza10=i_di10.id
WHERE  bolnickiracun.id_dijagnoza10!="" and i_di10.naziv is null
Comments