Retrieve data from two tables sql

Question

I am having 2 tables named table1, table2. table1 columns are t1_id, t1_name, t1_status. table2 columns are t1_id, t2_id, t2_name, t2_status. When I do Some operation in frontend t1_id will be inserted into table2. What I need is I want t1_id(s) from table1 which are not alloted or inserted in table2.

I've tried this query:

SELECT t1.t1_id, t1.t1_name
FROM table1 t1
LEFT OUTER JOIN table2 t2 
ON t1.t1_id != t2.t1_id

The problem with query is when all t1_id(s) are inserted into table2, then all t1_id(s) are showing again. How to resolve this issue? (I'am new to sql so please don't consider my mistakes.)

see "Left Excluding JOIN" at this reference http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins , & as you are new to SQL keep this reference handy — Paul Maxwell, Aug 12 '15 at 07:55

M Khalid Junaid · Answer 1 · 2015-08-12T07:49:34.483

1

Use is null to get rows from table1 which don't have any associations in table2

SELECT t1.t1_id, t1.t1_name
FROM table1 t1
LEFT OUTER JOIN table2 t2 
ON t1.t1_id = t2.t1_id
WHERE t2.t1_id IS NULL

edited Aug 12 '15 at 07:49

answered Aug 12 '15 at 07:46

M Khalid Junaid

63,861
10
90
118

one hopes that t1.id is already unique and hence selecting distinct is redundant in that case – Paul Maxwell Aug 12 '15 at 07:49
@Used_By_Already yes updated if there is no association then there will be no chance for duplicates – M Khalid Junaid Aug 12 '15 at 07:51

score 1 · Answer 2 · answered Aug 12 '15 at 07:47

1

If i understand your question right, this must b the query you need:

SELECT t1.t1_id, t1.t1_name
FROM table1 t1
LEFT OUTER JOIN table2 t2 
ON t1.t1_id = t2.t1_id
where t2.t1_id is null

answered Aug 12 '15 at 07:47

Jens

67,715
15
98
113

@chikku if it helps, you should accept on of the answers. – Jens Aug 12 '15 at 08:03

score 0 · Answer 3 · edited May 23 '17 at 11:44

0

I think this answers your questions: Select rows which are not present in other table

SELECT t1.t1_id, t1.t1_name
FROM table1 t1 
WHERE NOT EXISTS (
SELECT 1    --it's not so relevant what you put here
FROM table2 t2 
WHERE t1.t1_id = t2.t1_id
)

I hope this helps. ;-)

edited May 23 '17 at 11:44

Community

1
1

answered Aug 12 '15 at 07:52

Tech0

253
3
16

You should mark the question as duplicate and not copy the solution. – Jens Aug 12 '15 at 07:56

Retrieve data from two tables sql

3 Answers3