[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2013-01-12
12
Medium Priority
?
389 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 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
 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

649 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