How to combine select statements

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

How to combine select statements

Author

Topic

memory721
Starting Member

8 Posts

Posted - 2010-06-16 : 18:45:11

I have a query that I broke down into 3 separate select statements because I didn't know how to write it as a whole. I just don't know how to combine all 3 into one query.

Here are the 3 I want to combine and I want to add an Order by category_name at the end.

SELECT r.rule_name,
f1.value,
r.id
FROM rules r inner join flex_labels f1 on r.hierarchy_id = f1.hierarchy_id
WHERE f1.tablename='rules'
AND f1.fieldvalue = r.rule_name

SELECT rb.rule_builder_name,
f2.value
FROM rule_builders rb inner join rules r on rb.id=r.rule_builder_id
inner join flex_labels f2 on f2.hierarchy_Id = r.hierarchy_id
WHERE f2.tablename ='rule_builders'
AND f2.fieldvalue=rb.rule_builder_name

SELECT rc.category_name,
f3.value
FROM rule_categories rc inner join flex_labels f3 on rc.hierarchy_id=f3.hierarchy_id
WHERE f3.tablename='rule_categories'
AND f3.fieldvalue=rc.category_name

SQL user

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-16 : 20:21:13

[code]
SELECT r.rule_name as category_name,
f1.value,
r.id
FROM rules r inner join flex_labels f1 on r.hierarchy_id = f1.hierarchy_id
WHERE f1.tablename='rules'
AND f1.fieldvalue = r.rule_name
union all
SELECT rb.rule_builder_name,
f2.value
FROM rule_builders rb inner join rules r on rb.id=r.rule_builder_id
inner join flex_labels f2 on f2.hierarchy_Id = r.hierarchy_id
WHERE f2.tablename ='rule_builders'
AND f2.fieldvalue=rb.rule_builder_name
union all
SELECT rc.category_name,
f3.value
FROM rule_categories rc inner join flex_labels f3 on rc.hierarchy_id=f3.hierarchy_id
WHERE f3.tablename='rule_categories'
AND f3.fieldvalue=rc.category_name
Order by category_name
[/code]

KH
[spoiler]Time is always against us[/spoiler]

memory721
Starting Member

8 Posts

Posted - 2010-06-17 : 09:29:07

I ran the query you provided, but it didn't work. I got an error:
ORA-00904: "CATEGORY_NAME": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 20 Column: 9

SQL user

Sachin.Nand

2937 Posts

Posted - 2010-06-17 : 09:32:20

What is the whole query which you are using?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-06-17 : 09:39:33

You are using ORACLE, and this is a MS SQL SERVER forum.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

memory721
Starting Member

8 Posts

Posted - 2010-06-17 : 12:57:27

This is the original query. I wrote this in SQL

SELECT r.rule_name,
f1.value,
r.id
FROM rules r inner join flex_labels f1 on r.hierarchy_id = f1.hierarchy_id
WHERE f1.tablename='rules'
AND f1.fieldvalue = r.rule_name

SELECT rb.rule_builder_name,
f2.value
FROM rule_builders rb inner join rules r on rb.id=r.rule_builder_id
inner join flex_labels f2 on f2.hierarchy_Id = r.hierarchy_id
WHERE f2.tablename ='rule_builders'
AND f2.fieldvalue=rb.rule_builder_name

SELECT rc.category_name,
f3.value
FROM rule_categories rc inner join flex_labels f3 on rc.hierarchy_id=f3.hierarchy_id
WHERE f3.tablename='rule_categories'
AND f3.fieldvalue=rc.category_name

quote:
Originally posted by Idera

What is the whole query which you are using?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

SQL user

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-17 : 14:12:36

I think you're going to want somthing like this

SELECT t.rule_name,t.[value],t.id
(
SELECT r.rule_name as Category_name,
f1.value,
r.id
FROM rules r inner join flex_labels f1 on r.hierarchy_id = f1.hierarchy_id
WHERE f1.tablename='rules'
AND f1.fieldvalue = r.rule_name

UNION ALL

SELECT rb.rule_builder_name,
f2.value
FROM rule_builders rb inner join rules r on rb.id=r.rule_builder_id
inner join flex_labels f2 on f2.hierarchy_Id = r.hierarchy_id
WHERE f2.tablename ='rule_builders'
AND f2.fieldvalue=rb.rule_builder_name
UNION ALL
SELECT rc.category_name,
f3.value
FROM rule_categories rc inner join flex_labels f3 on rc.hierarchy_id=f3.hierarchy_id
WHERE f3.tablename='rule_categories'
AND f3.fieldvalue=rc.category_name

) t


ORDER BY category_name,value

The second and third parts of your query only have two columns, where your first query has 3. To make this work, each part of the query has to have the same number of columns, even if you have to supply a null.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources