CODEWITHSUNDEEP
sqlsql-serversql-server-2008
IAmHomes
IAmHomes

Reputation: 523

Inner Join with an And Operator and Or Operator SQL

I am looking for a query which has two operator condition, let say, i have 3 columns in table a and table now, i want to see result as if (a.id=b.id or a.cid=b.cid) And (a.site=b.site)

Table a

ID|CID|SITE
 1| 2 |sss
 2| 3 |ddd
 3| 4 |ddd

table b

ID|CID|SITE
 1| 2 |sss
 3| 3 |ddd
 3| 5 |sss

now the result should show,

ID|CID|SITE
 1| 2 |sss
 3| 3 |ddd

That means if (a.id=b.id AND a.site=b.site) OR (a.cid=b.cid AND a.sit=b.site)

Please help me out...

Upvotes: 0

Views: 8499

Answers (3)

Charles Bretana
Charles Bretana

Reputation: 146459

Although you don't specify which table to take the results from, your sample output indicates that you want to take it from table b, so:

 Select * From tableb b 
 Where exists
    (Select * From tablea
     Where site = b.site
        And (id = b.id or 
             cid = b.cid))

Upvotes: 3

2D3D4D
2D3D4D

Reputation: 131

I am not sure if I understand your question, what do you mean by the following:

a.d=b.id or a.cid=b.cid

assuming by a.d you mean a.id then you are trying to join on the IDs of the two tables. I will rather explain the SQL inner join for you, then you can try to make use of it.

There are 3 types of joins in SQL server, inner, outer, and both (full). I think you need inner join.

Inner Join returns all the matching record in table B for each record in table A.

SELECT * FROM TABLEA ta INNER JOIN TABLEB tb on tb.ID = ta.ID

Make sure you understand that if there are duplicate records in either table, the join will return repeating records.

Upvotes: 0

nurdglaw
nurdglaw

Reputation: 2117

How about this?

SELECT a.id,a.cid,a.site
FROM a
JOIN b ON a.id=b.id AND a.site=b.site
UNION ALL
SELECT a.id,a.cid,a.site
FROM a
JOIN b ON a.cid=b.cid AND a.site=b.site

or maybe this?

SELECT a.id, a.cid, a.site
FROM a JOIN b on a.site=b.site
WHERE a.id=b.id OR a.cid=b.cid

Upvotes: 1

Related Questions