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
Regex Remove All Occurrences of Multiple Characters in a String
"Missing Right Parenthesis": on Delete Set Null on Update Cascade (Sql/Oracle)
Left Outer Join and an Additional Where Clause
What Is the Effect of Omitting Size in Nvarchar Declaration
Issues with SQL Comparison and Null Values
Foreign Keys on Table from Different Database
SQL Duplicate Column Name Error
Percentiles from Histogram Data
What Does "&" Means in This SQL Where Clause
Convert One Row into Multiple Rows with Fewer Columns
Is There Any Function for Translating Data in SQL
SQL Server:Export Query as a .Txt File
Right Query to Get the Current Number of Connections in a Postgresql Db
How to Copy Data from One Column to Another in the Same Table
Remove the Last Character in a String in T-Sql
What Are the [Dis]Advantages of Using a Key/Value Table Over Nullable Columns or Separate Tables