CODEWITHSUNDEEP
mysqlsqlselectif-statement
ClydeFrog
ClydeFrog

Reputation: 1020

Get value even if it doesn't exist in table from SQL SELECT statement

I have a MySQL table that looks something like this:

|---ID---|---COUNTER---|
|   1    |      2      |
|   2    |      6      |
|   3    |      1      |
|   5    |      9      |
|   6    |      10     |

I'm looking for a SELECT statement that returns ID's and their COUNTER. The table only have ID's such as: 1,2,3,5,6. Is there a statement where you say: I want ID's 1 to 10 even if they doesn't exist in the table, and if the ID doesn't exist, return the ID anyway with the COUNTER value 0. For example:

|---ID---|---COUNTER---|
|   1    |      2      |
|   2    |      6      |
|   3    |      1      |
|   4    |      0      |
|   5    |      9      |
|   6    |      10     |
|   7    |      0      |
|   8    |      0      |
|   9    |      0      |
|   10   |      0      |

Do I have to create a SELECT statement that contains NOT EXIST parameters?

Thanks in advance, Steve-O

Upvotes: 1

Views: 2816

Answers (6)

Amar Palsapure
Amar Palsapure

Reputation: 9680

You can use left join to solve your issue. Read more about left join here

Upvotes: 2

Michał Powaga
Michał Powaga

Reputation: 23183

Without creating a temp table:

select t.num as id, coalesce(yt.counter, 0)
from your_table yt
right join (
    select 1 as num union select 2 union select 3 union select 4 union select 5 union
    select 6 union select 7 union select 8 union select 9 union select 10
) t on yt.id = t.num
order by t.num

and bit more general:

select t.num as id, coalesce(yt.counter, 0)
from your_table yt
right join (
    select t1.num + t2.num * 10 + t3.num * 100 as num
    from (
        select 1 as num union select 2 union select 3 union select 4 union select 5 union
        select 6 union select 7 union select 8 union select 9 union select 0
    ) t1
    cross join (
        select 1 as num union select 2 union select 3 union select 4 union select 5 union
        select 6 union select 7 union select 8 union select 9 union select 0
    ) t2
    cross join (
        select 1 as num union select 2 union select 3 union select 4 union select 5 union
        select 6 union select 7 union select 8 union select 9 union select 0
    ) t3
) t on yt.id = t.num
where t.num between (select min(id) from your_table) and (select max(id) from your_table)

You can define limit by yourself here I've used min and max of id value from your_table.

Upvotes: 2

Dervall
Dervall

Reputation: 5744

You need the range of integers to do an outer join with your table based on ID. Generating a range of integers is dependent on the SQL vendor if you do not want to use a temporary table. See SQL SELECT to get the first N positive integers for hints on how to do this based on your SQL vendor.

Upvotes: 1

Andomar
Andomar

Reputation: 238296

Create a table with all possible ID's:

create table Numbers (nr int primary key);

declare i int default 1;

while i < 100000 do
    insert into Numbers (nr) values (i);
    set i = i + 1;
end while;

Then you can use left join to return all numbers:

select  n.NR
,       c.Counter
from    Numbers n
left join
        Counters c
on      c.ID = n.NR

Upvotes: 2

dougajmcdonald
dougajmcdonald

Reputation: 20067

It's not very robust, but if you created a temporary table with the ID's you wanted in it, you could then left join to your table containing ID and Counter which would include all the values:

Declare @tempidtable as table ( imaginaryid int )

insert into @tempidtable ( imaginaryid ) values ( 1 )
insert into @tempidtable ( imaginaryid ) values ( 2 )
insert into @tempidtable ( imaginaryid ) values ( 3 )

select 
@temptable.imaginaryid,
ISNULL(yourothertable.counter, 0)
from @tempidtable
left join yourothertable
on @tempidtable.imaginaryid = yourothertable.id

As Tomek says you could loop over the inserts to make it easier to maintain, or possible store the ids you want as a base in another table, using this as the basis for the join rather than a temp table.

Upvotes: 2

Tomek
Tomek

Reputation: 3279

I think you will have to create (generate in loop) temporary table with the complete sequence of numbers from 1 to N (where N is the MAX(Id) of counted table). Then do left join to that table and apply GROUP BY clause.

Upvotes: 1

Related Questions