SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lack of Sub-Select Issue

    I converted a database from Access to MySQL recently. To make the import process easier (I was already using Navaicat which also made it a lot easier) I did not enforce the relationship integrity. Now, weeks later, I am trying to do that but am running into an issue.

    I have tables like so -
    JOBS (JobID,Title, StartDate,...)
    WORK_ORDERS (WorkID,JobID,Description,...)

    The problem is that Job ID's, the field connecting these two table, have gotten modified in one place and not the other. So, I'd like to run a couple of queries to A) find jobs that don't have a work order, and B) find work orders that don't have a job. This would be straight forward with sub-selects, but I don't have that with this version of MySQL.

    Any thoughts?
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Use a LEFT OUTER JOIN to get what you are looking for.

  3. #3
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But that will only give me records that are there, I need to know when it doesn't match up.
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com

  4. #4
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In other words...

    To select the jobs with no work orders -
    Code:
    Select JobID
    From Jobs
    Where JobID Not In (
      Select Distinct JobID
      From Work_Orders);
    To select the work orders that don't have a matching job ID -
    Code:
    Select WorkID
    From Work_Orders
    Where JobID Not In (
      Select JobID
      From Jobs);
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com

  5. #5
    SitePoint Member
    Join Date
    Jun 2004
    Location
    us of a
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In your code use nested Loop to cope for the lack of sub-selects. I think Mysql doc's also suggests this. Also because hard disks are cheaper these days so you can add more columns to your tables with redundant data instead of normalizing too much.
    I learned database using MS Access and then converted to MySql and immediately felt the pain because of lack of sub-selects/union/ joins /and many other sql stuff in mysql.
    Now I'm trying to convert from MySql to Firebird database.

    I use mysqlFront too and it does the job. Noticed one gotcha long ago that when you copy and paste table using mysqlfront, it fails to copy/add 'autoincrement' in new tables field defination. Hope they fixed it

    - slamdunkinpoolJAMyahooDOTcom ( remove 'JAM' )

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    To select the jobs with no work orders -
    Code:
    select J.JobID
      from Jobs J
    left outer
      join Work_Orders WO
        on J.JobID 
         = WO.JobID
     where WO.JobID is null
    To select the work orders that don't have a matching job ID -
    Code:
    select WO.JobID
      from Work_Orders WO
    left outer
      join Jobs J
        on WO.JobID 
         = J.JobID
     where J.JobID is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    slamdunkinpool, come on, your posts are starting to look like spam, that's the seventh post with your commercial for firebird

    give it up, please

    you are obviously just a beginner if you think that a "not exists" subquery is best solved by doing a query in a loop
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Jun 2004
    Location
    us of a
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, sorry about the Firebird...won't mention it again...but most likely you will be using it next year if not this year.
    And I'm not a MySql expert.
    Thought the original poster was a beginner like me.


    - slamdunkinpoolJAMyahooDOTcom ( remove 'JAM' )

  9. #9
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I learned database using MS Access and then converted to MySql and immediately felt the pain because of lack of sub-selects/union/ joins /and many other sql stuff in mysql.
    Well sub-selects are available from version 4.1 forward. What part of union and joins do you think are missing from MySQL even in version 3.2.* or whatever the stable version is?

  10. #10
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937 - Thanks for the help, again.
    daveman - I have version 4. 4.1 was stillin beta when I started this project, although, I considered using it anyway for the sub-selects.

    And yes, I am a newbie when it comes to MySQL.
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com

  11. #11
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Westmich, I should have specified, you did state you were a new user. slamdunkinpool made those claims and I wanted to see what rational they had for posting something that they obviously didn't bother to find out about before stating it so matter of factly.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •