SQL - SELECT rows NOT EXISTS in another table

Question

I'm trying to select the rows not present in table B, based on table A.

Unlike table B, table A has "_00" at the end of the titleid, and the column is called title instead of titleid.

Table A:

id | titleid
---+----------
1  | TEST1_00
2  | TEST2_00
3  | TEST3_00
4  | TEST4_00

Table B:

id | title
---+-------
1  | TEST1
2  | TEST2

I currently have:

SELECT `t1.titleid`
FROM `tableb t1`
LEFT JOIN `tablea t2` ON `t2.title + '_00' = t1.titleid`
WHERE `t2.title` IS NULL

How can I select the values which are present in A but not in B?

Desired output

id | title
---+----------
3  | TEST3_00
4  | TEST4_00

You list table "A" first in the `FROM` and check for the match on table "B". — Gordon Linoff, Mar 26 '19 at 19:59
This is obviously an easily found duplicate. [ask] Eg you could have googled your title. — philipxy, Mar 26 '19 at 21:27
Possible duplicate of [How to select all records from one table that do not exist in another table?](https://stackoverflow.com/questions/2686254/how-to-select-all-records-from-one-table-that-do-not-exist-in-another-table) — philipxy, Mar 26 '19 at 21:32
@philipxy Not with the complexity of this question. Did you downvote the question aswell? — Appel Flap, Mar 26 '19 at 22:35
Given your comment you still don't seem to have researched much. This neither shows research effort nor is helpful. Your last line is a misstatement of your first line, and neither your first line nor the rest explains "based on". So--"unclear. (It's good you gave an example though.) See the downvote arrow mouseover text. PS Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. [ask] [mcve] — philipxy, Mar 26 '19 at 23:49
Your use of back quotes is strange. MySQL puts them around table & column names. Please give a [mcve]. PS Learn what left join on returns: inner join on rows plus unmatched left table rows extended by nulls. Always know what inner join you want as part of a left join. — philipxy, Mar 27 '19 at 00:07
What question are you actually trying to ask? That you more or less understand the ON you want for your fuzzy match & the LEFT JOIN you want for non-matching rows, but that you can't find your typo? If so your question does not say that at all. — philipxy, Mar 27 '19 at 00:19

Welisson Carlos Dias · Answer 1 · 2019-03-27T02:58:43.580

2

It is possible to do this like that

SELECT `t1.titleid`
FROM `tablea t1`

WHERE 
NOT EXISTS (SELECT t2.title FROM `tableb t2` WHERE `t1.titleid = t2.title+ '_00'`)

edited Mar 27 '19 at 02:58

answered Mar 26 '19 at 20:24

Welisson Carlos Dias

27
5

Please before you post look at the formatted version of your post below the edit box. Read the edit help re inline & block formats for code & quotations. Please format your code reasonably. Read the edit help re code blocks. Also you have cut & pasted misuse of back quotes from the question. This is an example of why code questions need a [mcve] & without one should be downvoted & closed, not answered. – philipxy Mar 27 '19 at 00:04

MhQ-6 · Accepted Answer · 2019-03-27T14:39:14.697

1

 SELECT t1.titleid
 FROM tablea t1
 LEFT JOIN tableb t2 ON t2.title + '_00' = t1.titleid
 WHERE t2.title IS NULL

You want to pull Data from Table A , do a left join on Table B and pull data where TableB.Title is null.

Your Query was trying to pull data where TableA.Title is NULL.

edited Mar 27 '19 at 14:39

answered Mar 26 '19 at 20:10

MhQ-6

328
3
11

This is misusing backquotes that you copied from the question where they were being misused. – philipxy Mar 27 '19 at 00:21

score 1 · Answer 3 · answered Mar 26 '19 at 20:10

1

You need to LEFT JOIN tableb instead if tablea

SELECT `t1.titleid`
FROM `tablea t1`
LEFT JOIN `tableb t2` ON `t1.titleid = t2.title+ '_00'`
WHERE `t2.title` IS NULL

This will show which records in tablea don't have a match in tableb and are null

answered Mar 26 '19 at 20:10

isaace

3,336
1
9
22

score 0 · Answer 4 · edited Mar 27 '19 at 01:11

0

select * from A where SUBSTRING(A.title,0, 6) in (select B.title from B )

edited Mar 27 '19 at 01:11

Tommaso Bertoni

2,333
1
22
22

answered Mar 26 '19 at 21:13

user2328279

1
1

Please before you post look at the formatted version of your post below the edit box. Read the edit help re inline & block formats for code & quotations. Please format your code reasonably. Read the edit help re code blocks. PS You are making assumptions about matching that the question does not give. Unclear questions should be downvoted & closed, not answered. – philipxy Mar 27 '19 at 00:04
While this code may answer the question, providing additional context regarding how and/or why it solves the problem would improve the answer's long-term value. – undetected Selenium Mar 27 '19 at 05:49

SQL - SELECT rows NOT EXISTS in another table

4 Answers4