Solved

MySQL  - All from one table, not if in other based on 2 values

Posted on 2013-01-12
12
382 Views
Last Modified: 2013-01-22
HAve a table called Master, with a value called UniqueID.

Have a second table called Actions, where the field called ID is the same as the Master table's UniqueID.  (after a form processes, the UniqueID from Master is inserted into the Action table, along with an int called EventID

I want to include every record from Master, unless the UniqueID field matches the one in Action called ID AND the EventID field is the same as a variable passed by a form.  (it's in CF, I can handle the variable part, just not sure where to put it.)

I have this so far:

Select *
FROM Master a LEFT OUTER JOIN Actions b ON a.UniqueID = b.DealerID
WHERE isnull(`b`.`DealerID`)

But I don't know how to add on the rest.
0
Comment
Question by:Rush_2112
  • 6
  • 6
12 Comments
 
LVL 1

Author Comment

by:Rush_2112
ID: 38770700
I should mention that I only want records from Master excluded if they're already in the Actions table AND the EventID matches a variable.  If the UnquieID can be found in the Actions table, but alongside a different EventID, then I still want to see it.

Thanks
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38770827
If you always want to list the records from Master - even if there are 0 records for a given "UniqueID" in the actions table, try something like this (not tested)

SELECT  *
FROM     Master a LEFT JOIN Actions b
                      ON  a.UniqueID = b.DealerID
                      AND b.EventID =  <cfqueryparam value="#yourVariableHere#" ... >
<!--- ie when there's DealerID matches but
WHERE b.DealerID IS NULL

Otherwise, try using a NOT EXISTS clause

      SELECT  *
      FROM     Master a INNER JOIN Actions b ON  a.UniqueID = b.DealerID
      WHERE  NOT EXISTS (
                     SELECT c.DealerID
                     FROM    Actions c
                     WHERE  c.DealerID = b.DealerID
                     AND       c.EventID =  <cfqueryparam value="#yourVariableHere#" ... >
       )

*Note, need to fill in the "cfsqltype" in cfqueryparam
0
 
LVL 1

Author Comment

by:Rush_2112
ID: 38770889
I think you're close, but when I run it, I only get records from Master IF they already have records in ACTIONS.  I need everything in Master UNLESS the UniqueID appears in Actions and then ONLY if it's in Actions and the Events Variable is met (it's a number like 2)

Thank you for the help so far
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 38770897
Hm, the 1st one should give you that.  But maybe I'm misunderstanding. Let's say this is your data:

Master
UniqueID    
10
14
16

Actions
DealerID     | EventID
10              | 2
10              | 3
16              | 1

EDIT:  .. and you're filtering on EventID = 3 ie #yourVariableHere# = 3

The results of the 1st query would be:

        UniqueID
        14                         <--  doesn't exist in Actions at all
        16                         <--  exists in Actions, but NOT with eventID = 3

UniqueID = 10 is excluded because there *is* a matching record in Actions with EventID = 3
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38770922
(Please see my updated response). Hopefully the example above will tell you know if we're on the same page or not :) If not, let me know what results you'd like instead (and why)
0
 
LVL 1

Author Comment

by:Rush_2112
ID: 38770958
Right but the Master has thousands of records, and only the ones that appear in the actions table are showing.  Maybe this is supposed to be a Right outer join since I want all of the one table?
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 1

Author Comment

by:Rush_2112
ID: 38770968
That was it, I had to change it to a Left Outer Join.  But your code got me there!  Thanks so much!
0
 
LVL 1

Author Closing Comment

by:Rush_2112
ID: 38770969
great job!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38770972
No, a left join should give you exactly that.  Notice in my example UniqueID = 14 only exists in Master, not Actions. But 14 is included in the results.  (I just got a chance to test the example and can confirm it works as described).

Are you sure you're using the 1st example? ie

       SELECT  *
       FROM     Master a LEFT JOIN Actions b
                            ON  a.UniqueID = b.DealerID
                            AND b.EventID =  3
        WHERE b.DealerID IS NULL
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38770973
Whoops, sorry didn't see your replies before responding :) Glad it's solved.
0
 
LVL 1

Author Comment

by:Rush_2112
ID: 38784862
This is working, but it take a VERY long time to run the query, over 30 secs usually.  The 2 table involved aren't that big, is there something I could do to speed this up?

Thanks
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38806742
Sorry didn't this til now ..  (I usually stop monitoring once a thread is solved)

(Though  probably not the cause of the slowness, getting rid of "SELECT *" is always a good move. ) A query like this should not normally take long ..  

- How many rows are we talking about?
- Are there any other related columns between the 2 tables?
- Run the query in your db and look at the execution plan. It will tell you what is taking so long (table scan, cartesian product,etc...)
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 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

20 Experts available now in Live!

Get 1:1 Help Now