Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Other Forums
 Other Topics
 Informix SQL query from two tables

Author  Topic 

leo_ultra_leo
Starting Member

2 Posts

Posted - 2012-06-04 : 05:57:08
Hi folks,

I have two table, the first one name is basetab1 and the second is basetab2, as the following:

Table1:

select usertype Brands, count(*) Subscriber from basetab1
group by usertype
order by usertype

brands subscriber
--------- -------------
0 29029
1 2
2 21
3 175
4 467


Table2

select subtype ,subtypename from basetab2


isdntype isdntypename
----------- ----------------------------
0 Default
1 Employee
2 Test
3 Test2
4 Private


Question is, how can I get such result in one query:

isdntypename Subscriber
----------- -------------
Default 29029
Employee 2
Test 21
Test2 175
Private 467


I've tried these syntaxes but I've filed to get a quesry

First Syntax:
select isdntypename, count(*) MSISDN
for basetab1.usertype=basetab2.isdntypename
group by usertype



Second Syntax:

select isdntypename, count(msisdn) MSISDN
for basetab1 inner join basetab2 on (basetab1.usertype=basetab2.isdntypename)
group by usertype




Your HELP please!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-04 : 10:29:04
[code]
select isdntypename, Subscriber
from
(
select usertype Brands, count(*) Subscriber from basetab1
group by usertype
)t1
join basetab2 t2
on t2.isdntype = t1.brands
order by brands
[/code]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

leo_ultra_leo
Starting Member

2 Posts

Posted - 2012-06-05 : 02:54:40
Thanks dear, but didn't work as well . please remember this DB is informix not oracle. However, thanks for your effort

Cheers
quote:
Originally posted by visakh16


select isdntypename, Subscriber
from
(
select usertype Brands, count(*) Subscriber from basetab1
group by usertype
)t1
join basetab2 t2
on t2.isdntype = t1.brands
order by brands



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-05 : 12:41:15
quote:
Originally posted by leo_ultra_leo

Thanks dear, but didn't work as well . please remember this DB is informix not oracle. However, thanks for your effort

Cheers
quote:
Originally posted by visakh16


select isdntypename, Subscriber
from
(
select usertype Brands, count(*) Subscriber from basetab1
group by usertype
)t1
join basetab2 t2
on t2.isdntype = t1.brands
order by brands



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






then you should be posting it in some informix forum

this is ms sql server forum so i'm not sure there're any informix experts out here to help you with specific syntax help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

domusonline
Starting Member

1 Post

Posted - 2012-06-07 : 10:09:09
I'm not sure if my latest reply attemp will go through or not. It yes, please apologize for the duplicate:

The problem with the inline table is not that this is Informix. It's the version of Informix. Versions before 11 could use it but with a non-standard syntax.
In any case, the following query will work on any database:

SELECT
subtypename, COUNT(*)
FROM
basetab1 a, basetab2 b
WHERE
a.usertype = b.subtype
GROUP BY subtypename
ORDER BY subtypename;

There will be some problems if not all the records in basetab1 have a match in basetab2 (an outer join could be used if those records would need to be seen)

Also, there are some errors and incongruence in the original post ("for "?... SELECT subtype and then the result set shows isdntype?)

Regards and HTH
Go to Top of Page
   

- Advertisement -