Help Required for making a single Sql Query

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

General SQL Server Forums

New to SQL Server Programming

Help Required for making a single Sql Query

Author

Topic

usman2407
Starting Member

12 Posts

Posted - 2012-04-29 : 11:01:32

I am using following two queries to get data from tables 'cbolist' and 'beneficiarylist'

SELECT
cbolist.CBOName,beneficiarylist.BeneficiaryName
FROM cbolist
INNER JOIN beneficiarylist
ON cbolist.President=beneficiarylist.BeneficiaryNo
where cbolist.CBONo=192

SELECT
cbolist.CBOName,beneficiarylist.BeneficiaryName
FROM cbolist
INNER JOIN beneficiarylist
ON cbolist.VP=beneficiarylist.BeneficiaryNo
where cbolist.CBONo=192

The President and VP are on the Beneficiary list and have a Beneficiary No.

Can we make a single query of above queries to show the data in single row as following:

CBOName, President Name, VP Name
ABC, Mr. ABC, Mr.XYZ

Usman Ahmed

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-04-29 : 11:27:46

Are you able to re-design the database? It's probably a better idea to create one column in table, cbolist, called Position or JobType etc. Then store jobtypes in that column like so:

cbolist.JobType
Worker
Salesperson
FinanceOfficer
Director
VicePresident
President

If you can't do that though which would be a shame, alternatively, you could do something like this with your current database design:


SELECT 
cbolist.CBOName,beneficiarylist.BeneficiaryName
FROM cbolist P
INNER JOIN beneficiarylist B
ON P.President=beneficiarylist.BeneficiaryNo
where P.cboNo=192

union all

SELECT 
cbolist.CBOName,beneficiarylist.BeneficiaryName
FROM cbolist V
INNER JOIN beneficiarylist B
ON V.VP=beneficiarylist.BeneficiaryNo
where V.cboNo=192

There are various other techniques you could try, but it seems to be the database has some design problems.
EDITED ORIGINAL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-29 : 12:28:53

[code]
SELECT
P.CBOName,B.BeneficiaryName
FROM cbolist P
INNER JOIN (
SELECT BeneficiaryNo,BeneficiaryName
FROM beneficiarylist
UNPIVOT(BeneficiaryNo FOR Category IN ([President],[VP]))u
)B
ON P.BeneficiaryNo=B.BeneficiaryNo
where P.cboNo=192
[/code]

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

usman2407
Starting Member

12 Posts

Posted - 2012-04-29 : 13:05:26

Lets elaborate my question a little more:

I have a table 'cbolist'

I have a query

SELECT
CBOName,SO_ID,Basti,Muza,UC,Tehsil,District,President,VP,Secretary,AgriPerson,FinancePerson,BM1,BM2,BM3,TOB1,TOB2,BankAccountID,StationaryDistribution,Latitude,Longitude,ElevationFromSea,ElevationfromRiver
FROM `cbolist` WHERE CBONo=192

Now I want to show SO_Name instead of SO_ID from SOList Table
President, VP, Secretary, Agriperson, FinancePerson, BM1, BM2,BM3, TOB1,TOB2 are basically the designations of CBOList which contain BeneficiaryNo. They all have a unique beneficiaryNo in BeneficiaryList. I want to show the name of all of these designations from BeneficiaryList.

Please Help

Usman Ahmed

usman2407
Starting Member

12 Posts

Posted - 2012-04-30 : 03:40:55

Any body please help???

Usman Ahmed

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-30 : 08:01:52

If you still haven't found the relevant solution then it is because you haven't provided people here with adequate info about your requirement.

When you post a question make sure you post DDL of your tables, some readily consumable Data and your Expected Result Set from the Sample Data. There are a lot of people here who want to help you.
Please help them in helping you.
Please post DDL and sample data for better and faster solutions.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

usman2407
Starting Member

12 Posts

Posted - 2012-04-30 : 09:19:02

I have three tables: CBOList, BeneficiaryList and SO List

CBOList contains the list of all community based organizations.
I am dealing with 300 CBO's.
Attributes of CBO List are
CBONo,CBOName,SO_ID,Basti,Muza,UC,Tehsil,District,President,VP,Secretary,AgriPerson,FinancePerson,BM1,BM2,BM3,TOB1,TOB2,BankAccountID,StationaryDistribution,Latitude,Longitude,ElevationFromSea,ElevationfromRiver

BeneficiaryList contains all the Beneficiaries (People) who are member of any CBO.
Attributes of BeneficiariesList are
BeneificiaryNo, BeneficiaryName, Address, Age

SOList contains all the social organizers.
The attributes of SOList are
SO_ID, SO_NAMe, SO_Address

Each CBO has a President, VP, Secretary, Agriperson, FinancePerson, BM1, BM2,BM3, TOB1,TOB2.

e.g President column in CBOList will contain the BeneficiaryNo of the Person who is a member of the CBO and has a BeneficiaryNo in BeneficiaryList.

Now I want to show Details of CBO so that for selected CBO the Name of the President is shown instead of his beneficiaryno.

and same for VP, Secretary etc.

Plz Help

Usman Ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-30 : 09:51:40


SELECT t.CBONo,t.CBOName,
MAX(CASE WHEN Category = 'President' THEN bl.BeneficiaryName END) AS President,
MAX(CASE WHEN Category = 'VP' THEN bl.BeneficiaryName END) AS VP,
MAX(CASE WHEN Category = 'Secretary' THEN bl.BeneficiaryName END) AS Secretary,
...
FROM
(
SELECT CBONo,CBOName,BeneificiaryNo,Category,...
FROM CBOList c
UNPIVOT (BeneificiaryNo FOR Category IN ([President],[VP],[Secretary],[AgriPerson],[FinancePerson],...))u
)t
INNER JOIN BeneficiaryList bl
ON bl.BeneificiaryNo = t.BeneificiaryNo
GROUP BY t.CBONo,t.CBOName

if you had normalised your table and put those BeneficiaryNo as rows then you dont have to do all the above things. It would have been a simple join to get the values

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

usman2407
Starting Member

12 Posts

Posted - 2012-04-30 : 10:02:00

visakh16: My tables are normalized

Please tell me any simple join statement

Usman Ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-30 : 10:04:59

nope..as per posted sample data you've president etc values stored as columns.. so it wont work with simple join

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

usman2407
Starting Member

12 Posts

Posted - 2012-04-30 : 10:08:49

President, VP etc are the attributes of CBOList and contain the BeneficiaryNo of the Beneficiary who is President or VP

Usman Ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-30 : 10:11:01

quote:
Originally posted by usman2407

President, VP etc are the attributes of CBOList and contain the BeneficiaryNo of the Beneficiary who is President or VP

Usman Ahmed

thats why I told
if you're storing values as cross tabbed format in columns you've use solution i posted to first get them into rows and then do the join

Otherwise you will need a seperate join for getting each of designation Beneficiary names

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

usman2407
Starting Member

12 Posts

Posted - 2012-04-30 : 12:18:07

President,VP,Secretary,AgriPerson,FinancePerson,BM1,BM2,BM3,TOB1,TOB2

are the designations which a CBOMember(Beneficiary) Can have.

So Should I use a Attribute 'CBODesignation' in 'BeneficiaryList' Table ?

Usman Ahmed

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-30 : 23:59:58

Good job you did post the DDL of the tables now please post some Sample Data. The people who are trying to help you here need some data on which they can test their queries to check if that is the Solution.

Please provide Sample Data and help them.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

usman2407
Starting Member

12 Posts

Posted - 2012-05-01 : 03:33:05

Following is the sample data, I have reduced the columns of CBOList. CBO can be considered a group of people in a area.

Attributes of CBOList are
CBONo,CBOName,SO_ID,President,VP,Secretary
Following is the sample data for CBOList
192, Kot Karam Khan, 5, 10345, 10347, 10349

BeneficiaryList contains all the Beneficiaries (People) who are member of any CBO.

Attributes of BeneficiariesList are
BeneificiaryNo, BeneficiaryName, City, Age

Following is the sample data for BeneficiaryList
10345, Ahmed Ali, Rahim Yar Khan, 30
10346, Arif Hussain, Rahim Yar Khan, 31
10347, Muzamal Khan, Rahim Yar Khan, 25
10348, Suleman Akbar, Rahim Yar Khan, 36
10349, Asif Malik, Rahim Yar Khan, 37
10350, Usman Hashmi, Rahim Yar Khan, 33

SOList contains all the social organizers.
The attributes of SOList are
SO_ID, SO_Name, City
Following is the sample data for SOList
5, Ali Akbar, KhanBela

The Output Required is as following:
CBO Name, SO_Name, PresidentName, VPName,SecretaryName,
Kot Karam Khan, Ali Akbar, Ahmed Ali, Muzamal Khan, Asif Malik

Usman Ahmed

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-01 : 05:41:09

Thank you for posting the sample data. There isn't a simple JOIN query to do this. You will have to unpivot the data as Visakh was saying.
You can do it inside a CTE and get the result you want without actually altering your tables using the following query.


;With CTE
As
(SELECT b.CBONo,b.CBOName, b.SO_ID,
MAX(CASE WHEN Category = 'President' THEN c.BeneficiaryName END) AS President,
MAX(CASE WHEN Category = 'VP' THEN c.BeneficiaryName END) AS VP,
MAX(CASE WHEN Category = 'Secretary' THEN c.BeneficiaryName END) AS Secretary
FROM
(
SELECT CBONo,CBOName,BeneificiaryNo,Category, SO_ID
FROM CBOList c
UNPIVOT (BeneificiaryNo FOR Category IN ([President],[VP],[Secretary]))a
)b
INNER JOIN BeneficiariesList c
ON c.BeneificiaryNo = b.BeneificiaryNo
GROUP BY b.CBONo,b.CBOName,b.SO_ID)
Select y.CBOName, z.SO_Name, y.President As PresidentName, y.VP As VPName, y.Secretary As SecretaryName From CTE As y
JOIN SOList As z ON y.SO_ID = z.SO_ID

But, what you have got is not a good practice. So, every time you want to do something, you'll have to write such complex queries.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 09:53:27

quote:
Originally posted by vinu.vijayan

Thank you for posting the sample data. There isn't a simple JOIN query to do this. You will have to unpivot the data as Visakh was saying.
You can do it inside a CTE and get the result you want without actually altering your tables using the following query.

;With CTE
As
(SELECT b.CBONo,b.CBOName, b.SO_ID,
MAX(CASE WHEN Category = 'President' THEN c.BeneficiaryName END) AS President,
MAX(CASE WHEN Category = 'VP' THEN c.BeneficiaryName END) AS VP,
MAX(CASE WHEN Category = 'Secretary' THEN c.BeneficiaryName END) AS Secretary
FROM
(
SELECT CBONo,CBOName,BeneificiaryNo,Category, SO_ID
FROM CBOList c
UNPIVOT (BeneificiaryNo FOR Category IN ([President],[VP],[Secretary]))a
)b
INNER JOIN BeneficiariesList c
ON c.BeneificiaryNo = b.BeneificiaryNo
GROUP BY b.CBONo,b.CBOName,b.SO_ID)
Select y.CBOName, z.SO_Name, y.President As PresidentName, y.VP As VPName, y.Secretary As SecretaryName From CTE As y
JOIN SOList As z ON y.SO_ID = z.SO_ID

But, what you have got is not a good practice. So, every time you want to do something, you'll have to write such complex queries.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

why do you need the CTE and final join here?

the query that i posted would itself be sufficient for OPs output

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

usman2407
Starting Member

12 Posts

Posted - 2012-05-01 : 13:17:20

So should I use a attribute in BeneficiaryList called 'CBODesignation' and value can be President, VP or Secretary etc.

Usman Ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 14:20:27

quote:
Originally posted by usman2407

So should I use a attribute in BeneficiaryList called 'CBODesignation' and value can be President, VP or Secretary etc.

Usman Ahmedyep

Yep...store them rowwise with attribute containing type name and value containing their BeneficiaryNo

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

usman2407
Starting Member

12 Posts

Posted - 2012-05-01 : 23:42:56

Please give example

Usman Ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 23:47:37

something like


CBONo,CBOName,SO_ID,Designation,BeneficiaryNo
192, Kot Karam Khan, 5,President,10345 
192, Kot Karam Khan, 5,VP,10347
192, Kot Karam Khan, 5,Secretary,10349
..

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources