Informix SQL query from two tables

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/

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/

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/

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources