[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MySQL Query - one field blank where there should be data

Posted on 2012-09-17
8
Medium Priority
?
506 Views
Last Modified: 2012-09-17
Query follows:


SELECT SR_STU_ENROLL.CAMPUS_ID AS School,
      SR_STU_ENROLL.STATE_STU_ID AS Ssn,
      SR_STU_ENROLL.STU_ID AS StudId,
      SR_STU_ENROLL.NAME_F AS NameFirst,
      SR_STU_ENROLL.NAME_M AS NameMiddle,
      SR_STU_ENROLL.NAME_L AS NameLast,
      CONCAT(SR_STU_ENROLL.NAME_L,', ',SR_STU_ENROLL.NAME_F,' ',SR_STU_ENROLL.NAME_M) AS NameFull,
      SR_STU_ENROLL.GRD_LVL AS Grade,
      SR_STU_ENROLL.STATUS_CD AS `Status`,
      SR_STU_ENROLL.DT_ENTRY AS CurStatusDt,
      SR_STU_ENROLL.DT_ENTRY AS EntryDt,
      SR_STU_DEMO.DOB AS Birthday,
      CONCAT(SR_PARENT.PHONE_AREA_CD,SR_PARENT.PHONE_NBR) AS phone,
      CONCAT(SR_PARENT.NAME_L,', ',SR_PARENT.NAME_F,' ',SR_PARENT.NAME_M) AS ParentName,
      CONCAT(SR_PARENT.STR_NBR,' ',SR_PARENT.STR_NAME,' ',SR_PARENT.APT_NBR) AS ParentReside,
      CONCAT(SR_PARENT.CITY,' ',SR_PARENT.STATE_CD) AS ParentAddr,
      SR_PARENT.ZIP AS ParentZip5
FROM SR_PARENT INNER JOIN SR_STU_ENROLL ON SR_PARENT.STU_ID = SR_STU_ENROLL.STU_ID
       LEFT OUTER JOIN SR_STU_DEMO ON SR_STU_ENROLL.STU_ID = SR_STU_DEMO.STU_ID
WHERE SR_STU_ENROLL.SCH_YR = '2013'



It works fine but the Birthday fields are all empty. I looked directy into the table and it is definitely populated. How can I modify this query so I get the Birthday data?
0
Comment
Question by:smetterd
  • 3
  • 3
  • 2
8 Comments
 
LVL 3

Expert Comment

by:Oliver Wastell
ID: 38405409
Most of the fields are using ST_STU_ENROLL as the source table, but you are using SR_STU_DEMO as the source table for the Birthday - is this correct or should it also be ST_STU_ENROLL, i.e.
SR_STU_ENROLL.DOB AS Birthday
instead of
SR_STU_DEMO.DOB AS Birthday?
0
 
LVL 2

Author Comment

by:smetterd
ID: 38405429
i need to get dob out of sr_stu_demo since that's the only place it exists. Thx.
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38405554
The column being empty would indicate that the join is failing and the left join is returning null as no row was found where SR_STU_ENROLL.STU_ID = SR_STU_DEMO.STU_ID.  Are you sure you have the right join columns?
If you change it to an inner join ... are no rows returned?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 3

Expert Comment

by:Oliver Wastell
ID: 38405567
I've just been looking at the join and believe that this should work:
FROM (SR_PARENT INNER JOIN SR_STU_ENROLL ON SR_PARENT.STU_ID = SR_STU_ENROLL.STU_ID) LEFT JOIN SR_STU_DEMO ON SR_STU_ENROLL.STU_ID = SR_STU_DEMO.STU_ID;
0
 
LVL 2

Author Comment

by:smetterd
ID: 38405614
Tried odwastell idea, but it still outputs null for all Birthday

When I try

FROM SR_PARENT INNER JOIN SR_STU_ENROLL ON SR_PARENT.STU_ID = SR_STU_ENROLL.STU_ID
       INNER JOIN SR_STU_DEMO ON SR_STU_ENROLL.STU_ID = SR_STU_DEMO.STU_ID

Then the Birthday output is fine, but  I only get the first 3 records when there should be about 30,000 actually
0
 
LVL 25

Assisted Solution

by:lwadwell
lwadwell earned 1000 total points
ID: 38405640
>>  I only get the first 3 records when there should be about 30,000 actually
that is solid proof that the join is not working for all rows.

You will need to look at the values in SR_STU_ENROLL.STU_ID and SR_STU_DEMO.STU_ID and confirm that they are the same value.
0
 
LVL 3

Accepted Solution

by:
Oliver Wastell earned 1000 total points
ID: 38405655
I agree with lwadwell, it is definitely a join problem.
0
 
LVL 2

Author Comment

by:smetterd
ID: 38405722
Holy cow, the values are all null in SR_STU_DEMO.STU_ID !!!

Duh! No wonder...

Theres only a single student with a value for STU_ID

She was gtetting displayed thrice due the parent1, partent2, and parent3

But at any rate, I have reported this internal problem to data dept.

Thanks for all your help!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question