# Thread: Get record from table1 which not exist in table2

1. ## Get record from table1 which not exist in table2

hi to all,

I have two table for example

table a with one field a_id and second table b with three fields one is b_id,a_id,c_id

Now i want to get all records of table a whose a_id and a constant c_id is not in table b

i can get records of table a whose has a_id not in table b but i want to check the combination of a_id and c_id where c_id is any value for example constant.

For example

table a has records

a_id
1
2
3
4

table b has records
b_id a_id c_id
1 1 10
2 3 10

i want to get records form a
a_id
2
4

because these two records not exist in table b with c_id 10

get records from a where a.a_id not equal to b.a_id and b.c_id not equal to 10

regards.

2. Those two records don't exist in table b, so c_id does not exist. You can't impose a constraint on a value that doesn't exist. It's not equal to 10, but it's also not equal to 9, or 8, or anything.

So how does the nonexistant c_id come into the equation?

If table b had a row

3 2 10

Would you want record 2 then?

If table b had a row

3 2 9

Would you want record 2 then?

3 3 9

Would you now want record 3 as well?

If table b had a row

3 2 10

Would you want record 2 then?

yes i want to get record 2 in this case

If table b had a row

3 2 9

Would you want record 2 then?

no because in our query c_id will be 10 but c_id will change

3 3 9

Would you now want record 3 as well?

no same case with record 2

4. Your requirements are still in conflict.

Record 3 has a row in table A and B with CID 10, but you don't want 3, while you said you'd want record 2 if it had the same entry.

Code:
```Table A:

a_id
----
1
2
3
4
5

Table B:

b_id | a_id | c_id
-----------------
1    | 1    | 10
2    | 2    | 9
3    | 2    | 10
4    | 3    | 9
5    | 4    | 9```
Which rows do you want and why?

Or I can take a guess that you don't mean what you're saying:

Code:
```SELECT
a.a_id
FROM
a
LEFT OUTER JOIN
b
ON
a.a_id = b.b_id
WHERE
b.a_id IS NULL
OR
(b.a_id IS NOT NULL AND b.c_id = 10)```
Which would give you all rows of A which have no match in B, or do have a match with c_id 10.

5. thanks for guide me,

my requirement was
Code:
``` SELECT *

FROM a

LEFT JOIN b ON a.a_id = b.a_id

WHERE b.a_id IS NULL

OR (
b.a_id IS NOT NULL
AND b.c_id !=10
)
LIMIT 0 , 30```
thanks again

6. I have come across proble when table b has many entry so it return more than one time the result

so my requirement is

I have two table with following field and data
Table a
a_Id-----name
1--------A
2--------B
3--------C
4--------D

Table b
b_Id-----name-----C_id
1--------A---------10
2--------B---------10
3--------B---------9
4--------B---------8
5--------C---------7
6--------C---------6

I want to make a query which return all records from Table a who has no record in table b with combination of c_id which we suppose to 10
Sample query will be without join
Select * from a where a.name not equal to b.name and b.c_id not equal to 10;
But record from table a come once
Our final result will be
a_Id ----- name
3--------- C
4----------D

In table b we see name B come three time once with 10 and two time with 8,9 so I need if record come with B and c_id 10 then from table a does not show the record B.Record with name C in table b come twice but not with c_id 10 so it should come in final result once .D does not have entry in table b so I should come in final result

regards

7. Code SQL:
```SELECT
t1.a_id AS id
,t1.name AS name
FROM
a AS t1
WHERE
t1.name
NOT
IN
(
SELECT
DISTINCT t1.name
FROM
b AS t1
WHERE
t1.c_id = 10
)```

8. thanks work perfect.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•