CODEWITHSUNDEEP
mysqlselectwhere-clausemysql-workbench
Lazenbyt-mherPresident
Lazenbyt-mherPresident

Reputation: 33

Not getting (SELECT) all the results using where in MySql Database

Last night I had a question to make sure that I had formatted my php correctly to ensure I was able to test out the query. Today while working in mysql workbench I found that I was unable to get all the results I wanted. I have currently 15 rows in my "contact" table but when I run the following code, only 8 come through. Mind you I AM using multiple tables but some of these tables have more than one row per contact and some have no rows for some and multiple for others in the same table.

 SELECT 
`Contact`.`firstName`,
`Contact`.`lastName`,
`ssn`.`ssn`,
`Contact`.`country`,
`Allergies`.`allergy`,
`Allergies`.`allergyType`,
`Allergies_Contact`.`allergyNotes`,
`CurrentPrescriptions`.`prescriptionName`,
`CurrentPrescriptions`.`prescribedDate`,
`BloodType`.`bloodType`
FROM
`mher`.`Contact`,
`mher`.`Allergies_Contact`,
`mher`.`Allergies`,
`mher`.`ssn`,
`mher`.`CurrentPrescriptions`,
`mher`.`BloodType`
WHERE
`Contact`.`contactKey` = `Allergies_Contact`.`contactKey`
    AND `Allergies`.`allergiesKey` = `Allergies_Contact`.`allergiesKey`
    AND `ssn`.`contactKey` = `Contact`.`contactKey`
    AND `CurrentPrescriptions`.`contactKey` = `Contact`.`contactKey`
    AND `BloodType`.`contactKey` = `Contact`.`contactKey`;

enter image description here

Upvotes: 0

Views: 167

Answers (2)

Strawberry
Strawberry

Reputation: 33945

or - to my mind/eye, more readable...

SELECT c.firstName
     , c.lastName
     , ssn.ssn
     , c.country
     , a.allergy
     , a.allergyType,
     , ac.allergyNotes
     , pc.prescriptionName
     , pc.prescribedDate
     , bc.bloodType
  FROM Contact c
  LEFT
  JOIN Allergies_Contact ac
    ON ac.contactKey = c.contactKey 
  LEFT
  JOIN Allergies a
    ON a.allergiesKey = ac.allergiesKey
  JOIN ssn
    ON ssn.contactKey = c.contactKey
  LEFT
  JOIN CurrentPrescriptions
    ON pc.contactKey = c.contactKey
  JOIN BloodType
   AND bc.contactKey = c.contactKey;

Upvotes: 0

scragar
scragar

Reputation: 6824

Could you give this a go, I've made it a left join on the tables you don't need entries for:

  SELECT 
     `Contact`.`firstName`,
     `Contact`.`lastName`,
     `ssn`.`ssn`,
     `Contact`.`country`,
     `Allergies`.`allergy`,
     `Allergies`.`allergyType`,
     `Allergies_Contact`.`allergyNotes`,
     `CurrentPrescriptions`.`prescriptionName`,
     `CurrentPrescriptions`.`prescribedDate`,
     `BloodType`.`bloodType`
  FROM
     `mher`.`Contact`
     INNER JOIN `mher`.`ssn`
        ON `ssn`.`contactKey` = `Contact`.`contactKey`
     INNER JOIN `mher`.`BloodType`
        ON `BloodType`.`contactKey` = `Contact`.`contactKey`
     LEFT JOIN `mher`.`Allergies_Contact`
        ON `Contact`.`contactKey` = `Allergies_Contact`.`contactKey`
     LEFT JOIN `mher`.`Allergies`
        ON `Allergies`.`allergiesKey` = `Allergies_Contact`.`allergiesKey`
     LEFT JOIN `mher`.`CurrentPrescriptions`
        ON `CurrentPrescriptions`.`contactKey` = `Contact`.`contactKey`
  ;

Upvotes: 1

Related Questions