SQL Server : select from 2 tables

Question

I have this T-SQL statement

select a.CODE 
from MASTER..MASTERADD a, MASTER..MASTER b 
where a.TELO = '18002000047' 
  OR a.MOBILE = '18002000047' 
  AND b.TPIN = '42589' 
  and a.code = b.code

AND

select a.CODE 
from MASTER..MASTERADD a, MASTER..MASTER b 
where a.TELO = '18001147722' 
  OR a.MOBILE = '18001147722' 
  AND b.TPIN = '56783' 
  and a.code = b.code

The first statement returns 1 result (single line) (CODE)

The second returns 'CODE' but repeated like about 9000+ times.

What am I doing wrong?

A few things you are missing some parentheses around your `(a.TELO = '18001147722' OR a.MOBILE = '18001147722')`, second I'd strongly suggest using `JOIN` syntax instead of using a comma between your tables a the join criteria in a `where` clause. — Taryn, Jun 10 '14 at 15:53
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was discontinued with the ANSI-**92** SQL Standard (more than **20 years** ago) — marc_s, Jun 10 '14 at 15:57

score 2 · Accepted Answer · edited May 23 '17 at 12:05

2

The problem is the order of operations of the keywords OR and AND

What you need to do is add some parenthesis. You are most likely looking for this logic:

SELECT a.CODE
FROM MASTER..MASTERADD a
JOIN MASTER..MASTER b
    ON a.code = b.code
WHERE (
        a.TELO = '18001147722'
        OR a.MOBILE = '18001147722'
        )
    AND b.TPIN = '56783'

edited May 23 '17 at 12:05

Community

1
1

answered Jun 10 '14 at 15:52

arserbin3

6,010
8
36
52

score 0 · Answer 2 · answered Jun 10 '14 at 15:57

It looks like you need parentheses around your OR in the WHERE part of your statement:

SELECT
    MASTERADD.CODE
FROM
    MASTER..MASTERADD MASTERADD
    INNER JOIN
    MASTER..MASTER MASTER
    ON
        MASTERADD..CODE = MASTER.CODE
WHERE
    (
    MASTERADD..TELO = '18001147722'
    OR
    MASTERADD..MOBILE = '18001147722'
    )
    AND
    MASTER.TPIN = '56783'

This may not address your problem however. You have two different conditions (different telephone numbers and pin numbers), so why wouldn't you expect different results?

Less importantly, I would suggest you clean up the syntax on your code because it makes identifying problem areas and later reverse engineering easier to facilitate.

Actually the problem is this "The second returns 'CODE' but repeated like about 9000+ times." So, I get the code = "same code" but it's repeated like 9000+ times. — John Dcruz, Jun 11 '14 at 05:44

SQL Server : select from 2 tables

2 Answers2