Solved

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

Posted on 2013-01-12
12
383 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

895 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

14 Experts available now in Live!

Get 1:1 Help Now