Solved

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

Posted on 2013-01-12
12
387 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
Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

691 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