views:

61

answers:

3

I feel like I'm writing a word problem, but it's really puzzling me and I really hope someone here can solve it:

I want to select one row from table A. Table A includes the attributes Name and Number. But before I finish the query, I want to check it against table B. Table B includes Name, Number, and the Username of the user. Based on the users' input, it inserts rows into table B that include their Username along with a Name and Number. Now in my query where I select a row from table A, I want to make sure that there are no rows in table B with matching Name and Number for that particular User.

I have tried WHERE (A.Name = B.Name AND A.Number = B.Number AND B.Username != '$username') but I think I was way off base with that. Any help would be... amazing.

+2  A: 
SELECT
   A.id
FROM
   A
   LEFT OUTER JOIN B ON
      (A.Name = B.Name AND A.Number = B.Number)
WHERE
   B.Name IS NULL
   AND B.Number IS NULL
   AND B.Username = ?
tandu
This won't quite do it... See, there will be some rows in table B that include the username, but what I'm looking for is rows that don't. There could be like Robert-1-apple and Robert-2-apple and Robert-1-bravo, but what I need is Robert-1-charlie...
RobHardgood
Then also add B.Username IS NULL instead. I'm not exactly clear on what you want. Does A also have a username column? What is the general purpose of what you are trying to achieve?
tandu
A doesn't have a username column, just the "name" and "number" to match B. I'm still stumped.
RobHardgood
Or B.Username != ?
tandu
I need to learn more about joins first... my problem is that I'm already checking against some other factors in the same query... namely where A.value > 10 and such
RobHardgood
The LEFT OUTER JOIN is going to get all rows on A that do not have a matching name/number combo on B (name and number together, that is!). After that you should be able to work with the values as you please. Not 100% sure how the username fits in for you, though.
tandu
Hmmm, okay. Thanks, I'll see if i can work this out
RobHardgood
A: 
  IF NOT EXISTS  ( SELECT 1 
       FROM tableA a
       INNER JOIN tableB b
       ON a.name = b.name
       AND a.number= b.number
       AND b.UserName = 'user' and b.name = 'name'and b.number = 'number')
  SELECT *
  FROM tableA x
  WHERE x.name = 'name'and x.number = 'number'
InSane
+1  A: 
select a.id
from a
where
a.name=:name
and
not exists(select 1 from b where b.id=a.id and b.name=a.name)
xt.and.r
This looks like it might be helpful... can you explain the code?
RobHardgood
This simple select a.idfrom awherea.name=:name
xt.and.r
where tested existing this user in table b: not exists(select 1 from b where b.id=a.id and b.name=a.name)
xt.and.r
Thanks. I didn't use exactly this code, but "NOT EXISTS" was the command I was looking for. I didn't know about it, but it really helped.
RobHardgood