CODEWITHSUNDEEP
mysqlsql
Wonx2150
Wonx2150

Reputation: 149

SQL view not quite working

Here is my view statement:

    CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `seeallcontent` AS
    SELECT 
        `members`.`MemberID` AS `Member ID`,
        `members`.`FirstName` AS `FirstName`,
        `members`.`LastName` AS `LastName`,
        `centreaddress`.`Location` AS `Location`,
        `memlevel`.`level` AS `Membership`
    FROM
        (((`centreaddress`
        LEFT JOIN `centre` ON ((`centre`.`CAddress` = `centreaddress`.`id`)))
        LEFT JOIN `members` ON ((`centre`.`memberID` = `members`.`MemberID`)))
        JOIN (`memlevel`
        LEFT JOIN `membership` ON ((`membership`.`membershipID` = `memlevel`.`ID`))))

and it outputs all the members with their centres and then repeats the Type of membership as if every member had every membership type. I understand its doing this because i am not properly sorting the memlevel against the membersID however I cant get it to output both the centre and the memlevel and compare them to the members.

If i remove any mention of membership then i can get it to produce a perfectly good list of members and their centres.

This is an example of my databases ERD (i know you can auto generate them in most DBMS this is just a screenshot as i had to make one for my exam project anyway). I am trying to put bits of all 3 tables together. enter image description here

Upvotes: 0

Views: 71

Answers (2)

Wonx2150
Wonx2150

Reputation: 149

This is my final code based upon Thorsten Kettner answer:

  CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `seeallcontent` AS
    SELECT 
        `m`.`MemberID` AS `Member ID`,
        `m`.`FirstName` AS `FirstName`,
        `m`.`LastName` AS `LastName`,
        `ca`.`Location` AS `Location`,
        `ml`.`mlevel` AS `Membership`
    FROM
        ((((`members` `m`
        JOIN `membership` `ms` ON ((`ms`.`memberID` = `m`.`MemberID`)))
        JOIN `memlevel` `ml` ON ((`ml`.`ID` = `ms`.`mslevel`)))
        JOIN `centre` `c` ON ((`c`.`memberID` = `m`.`MemberID`)))
        JOIN `centreaddress` `ca` ON ((`ca`.`id` = `c`.`CAddress`)))

I had to change level to mslevel and mlevel as level is a reserved keyword and i also changed the values it was targeting as it was getting half the results.

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94969

First of all remove all those parentheses. That looks horrible. Like an MS Access query. Then your outer joins don't seem to make sense. You select center addresses and expect to find entries without related centers? Why would you? I think you'd better read up on outer joins again.

It would seem natural to select members with their data:

CREATE VIEW seeallcontent AS
SELECT 
  m.memberid AS "Member ID",
  m.firstname AS "FirstName",
  m.lastname AS "LastName",
  ca.location AS "Location",
  ml.level AS "Membership"
FROM members m
JOIN membership ms ON ms.memberID = m.memberID
JOIN memlevel ml ON ml.id = ms.levelid
JOIN centre c ON c.memberID = m.memberID
JOIN centreaddress ca ON ca.id = c.caddress;

Based on this query think about whether you need something else. Maybe include members without memberships? (Is this even possible? Would they be "members" then?) Well, then outer join ms and ml instead of inner joining them. Do you want members without centers? Then outer join c and ca.

Of course, for a member with three memberships and two centers this would give you 3 x 2 = 6 rows. It would combine every membership/level with every center/address, but how else could the result possibly look like? You'd have to restrict your results to, say, one center/address or one membership/level only, to avoid that multiplication. Or just don't write this view at all, if the result is not what you are after. Maybe this should better be two separate views instead.

Upvotes: 1

Related Questions