SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Other Forums
 Other Topics
 Informix SQL query from two tables		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

leo_ultra_leo
Starting Member

2 Posts
Posted - 06/04/2012 :  05:57:08  Show Profile  Reply with Quote
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

India
48103 Posts
Posted - 06/04/2012 :  10:29:04  Show Profile  Reply with Quote 

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

leo_ultra_leo
Starting Member

2 Posts
Posted - 06/05/2012 :  02:54:40  Show Profile  Reply with Quote
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

India
48103 Posts
Posted - 06/05/2012 :  12:41:15  Show Profile  Reply with Quote
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

Portugal
1 Posts
Posted - 06/07/2012 :  10:09:09  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000