0

I currently have two tables, "tc_services" and "tc_users" which both have a "USER_ID" field and "BILLING_ID" field. I need to query these together, finding any results where...

A) "tc_services" does not have a "BILLING_ID"

B) "tc_users" does not contain a "USER_ID" that matches that of the coorelating "tc_services"

I am trying to put together a JOIN query statement such as the following but not able to figure it out so far. Any help would be appreciated!

SELECT *
FROM tc_services AS services
JOIN tc_users AS users
WHERE services.USER_ID NOT IN users.USER_ID AND services.BILLING_ID = '';

Users

Services

  • I'm having trouble understanding what you're asking. Could you post the relevant table schemas, example data, and expected output? – Keeleon Mar 31 '16 at 16:28
  • Are you looking for services where there is no entry in users? Or an entry in users, but with no USER_ID. What is the foreign key between services and users? I do agree that table schema would be very helpful to see how to construct this query. – Daniel Humfleet Mar 31 '16 at 16:30
  • I added pictures of the schema to original post. Basically we are looking for entries in "tc_services" which A) Do not have a "BILLING_ID" value set and B) have a "USER_ID" value set that does not exist in the "tc_users" table. –  Mar 31 '16 at 16:34
  • Pseudo example would be "SELECT * FROM tc_services WHERE tc_services.billing_id = '' AND tc_services.user_id NOT EXISTS tc_users" –  Mar 31 '16 at 16:40

2 Answers2

0

Second Edit after seeing your comment:

Just do 

SELECT 
*
FROM tc_services 
WHERE tc_services.billing_id IS NULL AND
tc_services.user_id NOT IN (SELECT user_id FROM tc_users)

No joins needed

Edit: After rereading your question, you might want an 'LEFT JOIN'

see this picture: https://i.stack.imgur.com/66zgg.png

So your query(s) would look something like

SELECT 
* 
FROM tc_users u
LEFT JOIN tc_services s ON a.user_id = s.user_id
WHERE s.billing_id IS NULL

which would give you the list of users that don't have a billing ID.

and then similar for the second question.

Original Answer: Sounds like you might want to use a 'Union' rather than a 'Join'

Check out this related answer: What is the difference between JOIN and UNION?

Community
  • 1
  • 1
Tim
  • 184
  • 5
0

Something like:

SELECT SERVICE_ID, COUNT(users.USER_ID)
FROM tc_services AS services
LEFT JOIN tc_users AS users ON services.USER_ID = users.USER_ID
WHERE services.BILLING_ID = ''
GROUP BY services.SERVICE_ID HAVING COUNT(users.USER_ID) = 0
John Boker
  • 82,559
  • 17
  • 97
  • 130
  • the mysql syntax might be off a little, im usually working with sql server and this is not tested. – John Boker Mar 31 '16 at 16:42
  • Thank you for the help! I am getting an SQL error which appears to be between the "*)" and "users.USER_ID =" but trying to figure out what it is now. –  Mar 31 '16 at 16:47
  • Thanks! No more errors but no results where there should be quite a few. I think the issue is "ON users.USER_ID = services.USER_ID". We're trying to find where the "tc_services.USER_ID" has doesn't exist in "tc_users.USER_ID" –  Mar 31 '16 at 16:51
  • alright, found the issue, should only count the users.user_id, not * – John Boker Mar 31 '16 at 16:55
  • the way left join works is you'll get null values where the join couldnt be made. then you can use that information to further filter your query. – John Boker Mar 31 '16 at 17:01
  • I will just go ahead and accept this one as I believe it is correct, I am just not getting the proper results at the moment and trying to figure out why. I made a few edits to your response such as changing "services" to "tc_services" and "IS NULL" to "=' '" just to bring it more in line with what we are actually looking for. Hopefully can find the issue shortly. Thank you for your help :) –  Mar 31 '16 at 17:08