+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: SQL query with unique keys between two tables (/showthread.php?tid=12564)
SQL query with unique keys between two tables - El Forum - 10-23-2008
[eluser]jleequeen[/eluser]
I'm trying to figure out how to get a total between two tables but only counting a row once if it is in both tables. Assuming I have the following:
table1
id
0000
0001
0002
0010
table 2
id
0000
0004
0010
0011
I would want to do a count between the tables and only return the unique total between the two. So in the example above the answer should be: 6 (0000 and 0010 would only count once in the total since they are duplicated).
I'm using active record but not sure if I need to write a custom query to do this. Any insight?
SQL query with unique keys between two tables - El Forum - 10-23-2008
[eluser]davidbehler[/eluser]
You could use a union to do so.
In your example it would be something like:
Code:
(select id from table1)
union distinct
(select id from table2);And then just count the number of rows you get returned.