September 26, 2011 at 8:20 pm
I have to reireive the users with systemflag set to 'N'
tables:
1. table cmbusr(Only users with roletype having Users will have a record in this table)
column usrid ,cmproleid,loginname,systemflag
1 101 tst Y
2 104 grt N
3 105 hyt Y
2. table cmcomprl
column cmproleid ,roletype,cmpperid
101 User 200
102 Vendor 200
103 Customer 201
104 User 201
105 User 203
106 Vendor 203
107 client 204
108 Customer 205
3. table cmcomppers
column cmpperid , compname , firstName , lastName ,CompanyORIndividual
200 igate new igate 1 igate 2 Individual
201 iga iga 1 iga 2 Individual
203 manh manh1 manh2 Individual
204 igat igat 1 igat 2 Company
205 igate crest1 creat2 Company
IF i try to search for a company like 'iga' , it should return all the companyname staritin with 'iga' and all the individual(with companyname 'iga') having systemfg=N.
September 27, 2011 at 1:22 am
Please give an example output of the query you are wanting given the sample data you have provided. (If you can provide sample data in the form of ddl statements that makes it even easier for someone to help you.)
September 27, 2011 at 1:27 am
u can simply put inner join and get the desired results. hope this is what u want
select cmpusr.usrid,cmpusr.cmproleid,cmpusr.loginname,cmpusr.systemflag,cmpcomprl.roletype,cmpcomprl.cmpperid,cmcomppers.*
from cmpusr inner join cmpcomprl on cmpusr.cmproleid=cmpcomprl.cmproleid
inner join cmcomppers on cmcomppers.cmpperid=cmpcomprl.cmpperid
where cmcoppers.compname like 'iga%'
and cmppusr.systemflag='N'
October 25, 2011 at 9:59 am
OOps , this is not what i require.
October 27, 2011 at 10:42 am
would you please post your desire output what you want,with your proper DDL
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply