Rush_2112
asked on
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.
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.
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
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
ASKER
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
Thank you for the help so far
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
(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)
ASKER
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?
ASKER
That was it, I had to change it to a Left Outer Join. But your code got me there! Thanks so much!
ASKER
great job!
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
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
Whoops, sorry didn't see your replies before responding :) Glad it's solved.
ASKER
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
Thanks
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...)
(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...)
ASKER
Thanks