access columns from subquery

Posted on 2004-10-21
Last Modified: 2007-11-27

  Is there a way to get the columns from a subquery?  My query (tested and it works) is this:
       IF(LEFT(REPLACE(REPLACE(b.Pro,'-',''),' ',''),1)=0,SUBSTRING(REPLACE(REPLACE(b.Pro,'-',''),' ',''),2),REPLACE(REPLACE(b.Pro,'-',''),' ',''))  as Pro2,
       b.shipper, RIGHT(b.shipper,2) as shipper_state,
       b.consignee, RIGHT(b.consignee,2) as consignee_state,
       b.origin, b.dest,
       b.pieces, b.weight,
       b.charges, b.estdeldte,
       b.ststerm, b.pickupdte,
       b.colppd, b.appointment
FROM oo_ODFL_Tracking_1 as b
b.ststerm LIKE 'Call%'
                 ( SELECT c.pronum
                    FROM carriersettlement as c, masterquotes a
                  WHERE c.webquotecontrol = a.webquotecontrol AND
                             (c.pronum = b.Pro OR
                               c.pronum = Pro2 OR
                              a.webquotecontrol = OR
                              a.usercontrol = b.po) AND
                              (      IF(c.provider_approved IS NOT NULL,
                              c.provider_approved BETWEEN  b.charges-5 AND b.charges+5,
                               c.provider_estcost BETWEEN  b.charges-5 AND b.charges+5) ) AND
                              a.shipstate = shipper_state AND
                              a.consstate = consignee_state)

I want to pull the columns from the subquery, so that I can why the "b" table failed to show up in the subquery.  Example:  if the c.pronum was not found, I want to create a column in the return result that says failed_pronum=Y/N.

I wouldn't be to worried about understanding the last part.  The basic question is if there is a way to "show" c.pronum in the returned query?

Question by:bptacek
    LVL 5

    Expert Comment

    No, especially not with a NOT EXISTS query.  The entire subquery will evaluate to (TRUE) or (FALSE), and MySQL won't remember what was in it.

    Can I ask what type of application are you writing - like what language is sending this query to the database? If you need to perform the kind of procedural logic you described for failed_pronum, you'll need more than SQL.

    Author Comment

    I am using JasperReports with iReport.  If you don't know what that is, it is a report designer that will allow you to create reports.

    It uses Java, so I could code some Java.  The only problem is that I am not sure how extensive I can get with iReport.

    I did notice that you could do this

    SELECT AVG(sum_column1)
           FROM (SELECT SUM(column1) AS sum_column1
                 FROM t1 GROUP BY column1) AS t1;

    Something to that effect???  Is there a way to use that in conjunction with a GROUP BY yaddayadda HAVING something = "Y"?

    LVL 5

    Expert Comment

    Using a "SELECT FROM (SELECT FROM)" (unnamed view) syntax is different from using EXISTS, so I don't know if you could even implement the business logic with an unnamed view.

    Does JasperReports let you create subreports embedded within the main report, as MS Access does?

    Can you please describe in plain language your business requirement (eg: "I need to list all suppliers that have fewer than 5 open orders in a given state")? We might need to take a step back from the SQL you've got so far and see if there's a better approach, but to help with that I'll need to know the gist of the ogirinal requirement.


    Author Comment

    Yes, JasperReports does let you create subreports.  Currently that query IS running in  subreport..... hmmm... but maybe I could have a subreport with a subreport???  That might work.

    As far as what I am trying to do...  I am trying to compare tracking info to a local database.  So, pretend you download a tab file rom UPS (with all your shipments and their info), dump it into a table and then try to see if those shipments are different than the ones in the local DB.  For example.... the charges may be off in the local db, so I would like that shipment to show in the list and show why it is showing up... so it might say something like "charges flagged" or something.

    I am starting to think that I could maybe do that subreport of a subreport.  The hardest part will be on determining how to get the values from the subreport so that I can use it on it's parent report.

    LVL 5

    Accepted Solution

    You can usually link a subreport to its parent based on a field in the query.  You can also usually do multiple levels of subreports, eg subreports of subreports of subreports.

    Author Comment

    Havent had the time to try crimson117's idea, but in theory, it should work just fine.

    Thanks crimson!
    LVL 5

    Expert Comment

    No problem!  Good luck with your project.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    The Complete Ruby on Rails Developer Course

    Ruby on Rails is one of the most popular web development frameworks, and a useful tool used by both startups and more established companies to build strong graphic user interfaces, and responsive websites and apps.

    Both Easy and Powerful How easy is PHP? (  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
    Creating and Managing Databases with phpMyAdmin in cPanel.
    This video Micro Tutorial is the first in a two-part series that shows how to create and use custom scanning profiles in Nuance's PaperPort 14.5 ( But the ability to create custom scanning profiles al…
    This video discusses moving either the default database or any database to a new volume.

    846 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

    Need Help in Real-Time?

    Connect with top rated Experts

    5 Experts available now in Live!

    Get 1:1 Help Now