how to get value which not in table 2

Question

how to get table_1 id which is not in table_2
my table look like this type.
table_1

---------------------------------------------
|  id      |    user                        |
---------------------------------------------
|   1      |    Jack                        |
---------------------------------------------
|   2      |    John                        |
---------------------------------------------

table_2

------------------------------------------
|  web_id      |    website              |
------------------------------------------
|   2          |   Facebook              |
------------------------------------------
|   3          |   Google+               |
------------------------------------------

i want to codeigniter query

$this->db->select("*");
        $this->db->from("table_1");
        $this->db->where_not_in('id', "table_2.web_id");
        return $this->db->get();

How are these 2 tables related? – DevelopmentIsMyPassion Oct 07 '13 at 09:29 — DevelopmentIsMyPassion, Oct 07 '13 at 09:29
i have added queries for both ways below – plain jane Oct 07 '13 at 11:27 — plain jane, Oct 07 '13 at 11:27

plain jane · Answer 1 · 2013-10-07T11:26:59.377

2

Try this

 SELECT id
 FROM table_1
 WHERE id NOT IN
 (
       SELECT web_id FROM table_2 WHERE 1
 );

Using CodeIgniter Active Records the query will be as follows

  $this->db->select('*');
  $this->db->where('id NOT IN (SELECT web_id FROM table_2 WHERE 1)', NULL, FALSE);
  $query = $this->db->get('table_1');

edited Oct 07 '13 at 11:26

answered Oct 07 '13 at 09:30

plain jane

1,009
1
8
19

score 0 · Answer 2 · answered Oct 07 '13 at 09:29

0

Something like this would do it (depending on what flavour of SQL you're using you may have to adjust syntax slightly):

SELECT id
FROM table_1
WHERE id NOT IN (SELECT web_id FROM table_2)

However, I don't see what use this is. There's no identifiable link between the two tables, so knowing that there's an id of 1 in table_1 but no web_id of 1 in table_2 doesn't seem like a useful piece of information.

answered Oct 07 '13 at 09:29

Anthony Grist

38,173
8
62
76

yes its core query but when i am try it on codeigniter this query can you tell me what wrong in this query `$this->db->select("*"); $this->db->from($this->_table); $this->db->where_not_in('id', $this->_tuple.".tuple_regexs_id"); return $this->db->get();` – user2846831 Oct 07 '13 at 09:36
@user2846831 I don't code in PHP, so no I can't tell you what's wrong with that. If it's a problem with PHP code that you're having then you need to make that clear in the question (and make sure you include the relevant parts of the code!), because right now it reads as "What is the SQL to do this?" – Anthony Grist Oct 07 '13 at 09:39

score 0 · Answer 3 · answered Oct 07 '13 at 09:30

0

select t1.id
from table_1 t1
left join table_2 t2 on t1.id = t2.web_id
where t2.web_id is null

answered Oct 07 '13 at 09:30

juergen d

201,996
37
293
362

how to get value which not in table 2

3 Answers3