MySQL create Tables/Views

Question

I don't know if the title is correct but here goes my problem. So, I want to create View in phpmyadmin using data from several tables (picture below), View that represents maintenance for lamps with fields from several tables (substation, post_type, area, lamp_type, failure and maintenance)

Here are tables with connections:

I've manage somehow and created something like this(picture below), which I managed to create using this block of code:

      CREATE OR REPLACE VIEW
v_lamp_I
AS
SELECT
lamps.id,
substation.code AS sub_code,
substation.name AS sub_name,
lamps.lamp_code,
post_type.descript AS post_ty,
lamp_type.descript AS lamp_ty,
area.descript AS area_name,
rasvjeta.adress,
DATE_FORMAT(date_maintenance, "%d.%m.%Y.") AS date_main, 
lamps.geo_long,
lamps.geo_lat
FROM lamps
INNER JOIN substiation ON substiation.id = lamps.substiation_id
INNER JOIN post_type ON post_type.id = lamps.post_type_id
INNER JOIN lamp_type ON lamp_type.id = lamps.lamp_type_id
INNER JOIN area ON area.id = rasvjeta.area_id
INNER JOIN maintenance ON maintenance.lamps_id = lamps.id

I've managed to create view but the problem is with that view I can see only rows/lamps(sifra_lampe) which were maintained, only 4. In table lamps I've 24 entries in and only 4 entries for maintenance.But, I want to see all 24 entries and if there was no maintenance for that particular lamp, field can be empty with date format (00-00-00 or it can be NULL) and for entries/lamps that were maintained I want to be visible date field.

Here is table lamps with entries.

And here is view with maintenance date. As you can see there are only 6 entries

I want to see the rest of the entries, for lamps that were not maintained entries can be null or date format like this (00-00-00) and for lamps that were maintained date format can stay the same, in short I want to see all entries not only those which were maintained. Thank you and sorry for long question. I didn't know how to construct meaningful and short question so wrote everything.

I can't make much sense of your table names so I'll leave the table connection to you, but you should look into Joins to get the view you want. Inner join only shows full matches (where both tables have values), you should use Left join (which shows all rerecords from primary table and selected from secondary). With left join your first table should be the one that has the 24 records you want to display and secondary table the one that has the "null" values. Answer here will help you: http://stackoverflow.com/questions/406294/left-join-and-left-outer-join-in-sql-server — Zero, Mar 15 '16 at 07:52

score 1 · Answer 1 · answered Mar 15 '16 at 09:39

1

CREATE OR REPLACE VIEW
v_lamp_I
AS
select tbl_lamp.*,DATE_FORMAT(date_maintenance, "%d.%m.%Y.") AS date_main 
from (SELECT lamps.id,
substation.code AS sub_code,
substation.name AS sub_name,
lamps.lamp_code,
post_type.descript AS post_ty,
lamp_type.descript AS lamp_ty,
area.descript AS area_name,
rasvjeta.adress,
lamps.geo_long,
lamps.geo_lat
FROM lamps
INNER JOIN substiation ON substiation.id = lamps.substiation_id
INNER JOIN post_type ON post_type.id = lamps.post_type_id
INNER JOIN lamp_type ON lamp_type.id = lamps.lamp_type_id
INNER JOIN area ON area.id = rasvjeta.area_id) as tbl_lamp
LEFT JOIN maintenance ON tbl_lamp.id = maintenance.lamps_id

answered Mar 15 '16 at 09:39

user3436157

86
4

@Svinjica try this one – user3436157 Mar 15 '16 at 09:42
I did, and I get this #1349 - View's SELECT contains a subquery in the FROM clause. 3 errors were found during analysis. 'An expression was expected. (near "(" at position 119) Unexpected token. (near "(" at position 119) This type of clause was previously parsed. (near "SELECT" at position 121)' – Svinjica Mar 15 '16 at 10:08

score 1 · Accepted Answer · answered Mar 15 '16 at 10:25

1

create or replace view 
v_lamp_I as 
SELECT lamps.id,
substation.code AS sub_code,
substation.name AS sub_name,
lamps.lamp_code,
post_type.descript AS post_ty,
lamp_type.descript AS lamp_ty,
area.descript AS area_name,
rasvjeta.adress,
lamps.geo_long,
lamps.geo_lat
FROM lamps
INNER JOIN substiation ON substiation.id = lamps.substiation_id
INNER JOIN post_type ON post_type.id = lamps.post_type_id
INNER JOIN lamp_type ON lamp_type.id = lamps.lamp_type_id
INNER JOIN area ON area.id = rasvjeta.area_id;


CREATE OR REPLACE VIEW
v_lamp_new
AS
select v_lamp_I.*,DATE_FORMAT(date_maintenance, "%d.%m.%Y.") AS date_main 
from v_lamp_I
LEFT JOIN maintenance ON v_lamp_I.id = maintenance.lamps_id;

answered Mar 15 '16 at 10:25

user3436157

86
4

@Svinjica - I was make mistake, we cant use subquery in view. Try this one. – user3436157 Mar 15 '16 at 10:27
It works, thx ! Is it possible that instead of NULL value writes some string, like "There was no maintenance?" – Svinjica Mar 15 '16 at 10:42
1

yes we can , use IFNULL(DATE_FORMAT(date_maintenance, "%d.%m.%Y."),'There was no maintenance') AS date_main instead of DATE_FORMAT(date_maintenance, "%d.%m.%Y.") AS date_main – user3436157 Mar 15 '16 at 12:24

MySQL create Tables/Views

2 Answers2