Check If Entry in Table a Exists in Table B

Check if entry in table A exists in table B

SELECT *
FROM B
WHERE NOT EXISTS (SELECT 1
FROM A
WHERE A.ID = B.ID)

Using SQL check to see if an ID in Table A exists in Table B. If that ID exists in Table B return corresponding value

Assuming you want to skip any values in TableA that do not have a corresponding value in TableB, an INNER JOIN would suffice:

SELECT 
TABLEA.ID1,
TABLEB.Value
FROM
TABLEA
INNER JOIN TABLEB ON TABLEA.ID1 = TableB.ID

If you do want to show values existing in TableA, that do not exist in TableB, change the INNER JOIN to a LEFT JOIN:

SELECT 
TABLEA.ID1,
TABLEB.Value
FROM
TABLEA
LEFT JOIN TABLEB ON TABLEA.ID1 = TableB.ID

If you have a situation like prior to the edit, where multiple columns exist in tableA that can correspond with the ID in TableB, you could go with Andrew's suggestion of using OR in the join condition.

SQL, Determine if Records in Table A exist in Table B

You can use not exists. The following query gives you all records in tablea that cannot be found in tableb:

select a.*
from tablea a
where not exists (
select 1
from tableb b
where a.accountNum = b.accountNum and a.routingNum = b.routingNum
)

This assumes that you want to match on both columns (which is what your description suggests). You can adapt the where conditions in the subquery to match on one column only.

checking if a value exists in another table within the SELECT clause

Using subquery in SELECT CASE will cost more. Use left join instead like below

    select A.name, 
CASE WHEN B.name IS NOT NULL
THEN 'common'
ELSE 'not common'
END

from table1 A
left join table2 B
on A.name = B.name

SQL - join TWO tables and get record from B table if exist, else from A table

If you always have a value in table A, this should do it:

SELECT t.id, C.status FROM (
SELECT
A.id, coalesce(B.some_value, A.some_value) as some_value
FROM
A
LEFT JOIN B ON B.id = A.id
) t
INNER JOIN C on C.id = t.some_value;

Here's a fiddle: http://sqlfiddle.com/#!9/1762f35/4

p.s. I'm not sure I got your data model right, though

UPD:

In case you need to join by id, not some_value, here's another fiddle (it's the same approach): http://sqlfiddle.com/#!9/f2be17/1

Select from table A which does not exist in table B with condition in table B

Try this;)

select t1.*
from tableA t1
left join tableB t2
on t1.id = t2.tableA_id
where t2.setting <> 0 or t2.id is null

DEMO HERE

Or this:

select *
from tableA
where not exists (
select 1 from tableB where tableA.id = tableB.tableA_id and tableB.setting = 0
)

DEMO HERE

Check if table a primary key is exist in table b

select A.*, IFNULL((select 1 from B where B.TableA-ID = A.ID limit 1),0) as `exists` from A;

The above statement will result in a 1, if the key exists, and a 0 if that key does not exist. Limit 1 is important if there are multiple records in B



Related Topics



Leave a reply



Submit