Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

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

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
Rush_2112
Asked:
Rush_2112
  • 6
  • 6
1 Solution
 
Rush_2112Author Commented:
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
 
_agx_Commented:
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
 
Rush_2112Author Commented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
_agx_Commented:
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
 
_agx_Commented:
(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
 
Rush_2112Author Commented:
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
 
Rush_2112Author Commented:
That was it, I had to change it to a Left Outer Join.  But your code got me there!  Thanks so much!
0
 
Rush_2112Author Commented:
great job!
0
 
_agx_Commented:
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
 
_agx_Commented:
Whoops, sorry didn't see your replies before responding :) Glad it's solved.
0
 
Rush_2112Author Commented:
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
 
_agx_Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now