Lack of Sub-Select Issue

[westmich]

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?

[guelphdad]

Use a LEFT OUTER JOIN to get what you are looking for.

[westmich]

But that will only give me records that are there, I need to know when it doesn't match up.

[westmich]

In other words...

To select the jobs with no work orders -

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 -

Select WorkID
From Work_Orders
Where JobID Not In (
  Select JobID
  From Jobs);

[slamdunkinpool]

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' )

[r937]

To select the jobs with no work orders -

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 -

select WO.JobID
  from Work_Orders WO
left outer
  join Jobs J
    on WO.JobID 
     = J.JobID
 where J.JobID is null

[r937]

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

[slamdunkinpool]

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' )

[guelphdad]

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?

[westmich]

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.

[guelphdad]

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.