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

Question

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

@JonH Those are the default MySQL escape characters, most databases use [], mysql uses `` — scragar
– scragar, Commented Jul 28, 2014 at 16:15
After re-reading your question it sounds like you want entries where there are no results for some tables, is that correct, and if so which tables? — scragar
– scragar, Commented Jul 28, 2014 at 16:21
This is correct, the Allergy tables (Allergies and Allergy_Contact) and the prescription table — Lazenbyt-mherPresident
– Lazenbyt-mherPresident, Commented Jul 28, 2014 at 16:22

scragar · Accepted Answer · 2014-07-28 16:26:11Z

1

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`
  ;

answered Jul 28, 2014 at 16:26

scragar

6,84530 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lazenbyt-mherPresident Over a year ago

that worked, can you explain why solution wasn't working and why yours did

scragar Over a year ago

Certainly, you were doing what is called a qualified cross join, which is exactly the same as the explicit joins I've replaced it with above, with one big exception, using a LEFT JOIN says if the matching row doesn't exist join me to an all null row instead(where an INNER JOIN or WHERE condition would discard the row).

Lazenbyt-mherPresident Over a year ago

This makes sense, I knew that there was something else missing from my arguments, but reading the documentation for mysql queries I was having a hard time figuring out how to bring in the null rows

Strawberry · Accepted Answer · 2014-07-28 16:34:36Z

0

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;

answered Jul 28, 2014 at 16:34

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related