We help IT Professionals succeed at work.

SQL Join Querry

hdcowboyaz
hdcowboyaz asked
on
292 Views
Last Modified: 2012-02-16
The below query pulls in data but omits rows if the po table has missing data. It still should pull in the rest of the columns even if that column is empty or NULL.

SELECT blah, blah, blah
FROM estdetail d
              LEFT JOIN (
                  emp e
                  JOIN est ON (e.emp_id = est.emp_id_created)
              ) ON (est.est_id = d.est_id)
              LEFT JOIN (
                  funvert f
                  JOIN task t ON (f.funvert_id = t.funvert_id)
                  JOIN releases r ON (r.releases_id = t.releases_id)
                  JOIN project pj ON (pj.project_id = r.project_id)
                  JOIN program pg ON (pg.program_id = pj.program_id)                  
                  JOIN stdtask s ON (s.stdtask_id = t.stdtask_id)                
              ) ON (t.task_id = d.task_id)
              LEFT JOIN (
                  icat ic
                  JOIN item i ON (ic.icat_id = i.icat_id)
                  JOIN itype it ON (it.itype_id = i.itype_id)
                  JOIN mc ON (mc.mc_id = i.mc_id)                      
              ) ON (i.item_id = d.item_id)
              LEFT JOIN (
                  pr
                  JOIN prtrans prt ON (pr.pr_id = prt.pr_id)
                  JOIN vendor v ON (v.vendor_id = prt.vendor_id)
                  JOIN cc ON (cc.cc_id = prt.cc_id)                  
                  JOIN po ON (po.po_id = prt.po_id)
              ) ON (prt.estdetail_id = d.estdetail_id)                          
WHERE (d.deleted != 'Y' OR d.deleted IS NULL);
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Try
LEFT JOIN po ON (po.po_id = prt.po_id)
instead of
JOIN po ON (po.po_id = prt.po_id)
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Good day!

Yes, it will omit the data from pr, prtrans, vendor, and cc tables because of the INNER JOIN. Try:

SELECT blah, blah, blah
FROM estdetail d
              LEFT JOIN (
                  emp e
                  JOIN est ON (e.emp_id = est.emp_id_created)
              ) ON (est.est_id = d.est_id)
              LEFT JOIN (
                  funvert f
                  JOIN task t ON (f.funvert_id = t.funvert_id)
                  JOIN releases r ON (r.releases_id = t.releases_id)
                  JOIN project pj ON (pj.project_id = r.project_id)
                  JOIN program pg ON (pg.program_id = pj.program_id)                  
                  JOIN stdtask s ON (s.stdtask_id = t.stdtask_id)                
              ) ON (t.task_id = d.task_id)
              LEFT JOIN (
                  icat ic
                  JOIN item i ON (ic.icat_id = i.icat_id)
                  JOIN itype it ON (it.itype_id = i.itype_id)
                  JOIN mc ON (mc.mc_id = i.mc_id)                      
              ) ON (i.item_id = d.item_id)
              LEFT JOIN (
                  pr
                  JOIN prtrans prt ON (pr.pr_id = prt.pr_id)
                  JOIN vendor v ON (v.vendor_id = prt.vendor_id)
                  JOIN cc ON (cc.cc_id = prt.cc_id)                  
                  LEFT JOIN po ON (po.po_id = prt.po_id)
              ) ON (prt.estdetail_id = d.estdetail_id)                          
WHERE (d.deleted != 'Y' OR d.deleted IS NULL);

Note: if other tables involved may not have data, but you want to see the other rows for the main table, e.g., in the po grouping the main table appears to be prtrans. You may want to have pr, vendor, and cc also connected via LEFT JOINs if the absence of rows in those tables should not affect the display of prtrans rows.

I hope that helps!

Best regards and happy coding,

Kevin

Author

Commented:
I am re-writing it.  I will let you know.

Author

Commented:
I think I need a self-join?  The query below works except I'm pulling two names from the emp e table. I get the same names in both columns 'SUBMITT BY' &  'BEHALF OF' even though they have different emp_id's in the emp table.

SELECT CONCAT(pr.prtype, pr.pr_num) PN, prt.pr_line 'PR LINE', pr.pstatus 'STATUS', DATE_FORMAT(pr.submitt_date, '%m/%d/%y') 'SUMITT DATE',
CONCAT(e.efirst,' ',e.elast) 'SUBMITT BY', CONCAT(e.efirst,' ',e.elast) 'BEHALF OF', po.po_num 'PO', prt.po_line 'PO LINE'
FROM prtrans prt
LEFT JOIN pr ON (pr.pr_id = prt.pr_id)
LEFT JOIN po ON (po.po_id = prt.po_id)
LEFT JOIN vendor v ON (v.vendor_id = prt.vendor_id)
LEFT JOIN cc ON (cc.cc_id = prt.cc_id)
LEFT JOIN estdetail d ON (prt.estdetail_id = d.estdetail_id)
LEFT JOIN est ON (est.est_id = d.est_id)
LEFT JOIN emp e ON (e.emp_id = est.emp_id_created)
LEFT JOIN (
            funvert f
            LEFT JOIN task t ON (f.funvert_id = t.funvert_id)
            LEFT JOIN releases r ON (r.releases_id = t.releases_id)
            LEFT JOIN project pj ON (pj.project_id = r.project_id)
            LEFT JOIN program pg ON (pg.program_id = pj.program_id)                  
            LEFT JOIN stdtask s ON (s.stdtask_id = t.stdtask_id)                
           ) ON (t.task_id = d.task_id)
LEFT JOIN (
            icat ic
            LEFT JOIN item i ON (ic.icat_id = i.icat_id)
            LEFT JOIN itype it ON (it.itype_id = i.itype_id)
            LEFT JOIN mc ON (mc.mc_id = i.mc_id)                      
           ) ON (i.item_id = d.item_id)
WHERE (prt.deleted != 'Y' OR prt.deleted IS NULL)
AND prt.R_AMT_CY != 0
ORDER BY pr.submitt_date;

Author

Commented:
emp Table

emp_idmediumint(10) unsigned NOT NULL
efirstvarchar(25) NULL
elastvarchar(25) NULL
bu_idmediumint(10) unsigned NULL
euservarchar(50) NOT NULL
etypevarchar(25) NULL
emailvarchar(50) NULL
emp_id_managermediumint(10) unsigned NULL
role_idmediumint(10) unsigned NULL
emp_numvarchar(25) NULL
qid_numvarchar(25) NULL
hire_datedate NULL
term_datedate NULL
loc_idmediumint(10) unsigned NULL
loginvarchar(32) NULL
app_roleenum('A','P','R','System Admin') NULL
cc_idmediumint(10) unsigned NULL
funvert_idmediumint(10) unsigned NULL
timestamp_ctimestamp NULL
emp_id_createdmediumint(10) unsigned NULL
timestamp_mtimestamp NULL
deletedenum('Y','N') NULL
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
I think I was not clear. I thought I highlighted specifically what should be changed as did fomand, but it appears it caused confusion. Why did you change the query that drastically?
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
please read my comment about the prtrans table and the specific tables joins that should be reviewed again...maybe it will be more clear this time.

Author

Commented:
The query was initially poorly written. It was off the prtrans table not the estdetail table.  That is why I rewrote it.  It works now except I'm trying to figure out the emp table detail

Thanks
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
okay. join est and emp to estdetail like some of the others...then check if there is another column that need to be in the join to make the connection unique.

Author

Commented:
I forgot to mention the two emp table relationships are:

(e.emp_id = est.emp_id_created)
(e.emp_id = est. emp_id_behalf)
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Good. :)
Best regards,

Kevin

Author

Commented:
I figured it out...

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.