[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 544
  • Last Modified:

Using an SQL select query to get data from 3 different tables...

I have 3 database tables: Mem, Gen, and Gre
Within these tables each has a field called Name.

Some names are in both Mem and Gen.
No names in Gre are in the other two tables.

I want to create a SQL Select query to list the names from all 3 of the tables in the following order.
- All Name's in Mem
- All Name's in Gen that aren't also in Mem
- All Name's in Gre
 
0
Dynamite149
Asked:
Dynamite149
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Dynamite149,

This sure sounds like ahomework assignment.  :(  So instead of giving you the SQL, here's a huge hint.  Use UNION

SELECT 1, NAME from mem
UNION ALL
SELECT 2, NAME from gen WHERE (condition)
UNION ALL
SELECT 3, NAME from gre WHERE

Fill in the condition, sort by the integer value, and print the names.


Good Luck!
Kent
0
 
Dynamite149Author Commented:
Not a homework assignment I assure you....  Teaching myself a bit of SQL for a database i'm trying to build..
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Dynamite149,

Ok then.  :)  


SELECT name
FROM
(
  SELECT 1 as position, NAME from mem
  UNION ALL
  SELECT 2 as position, NAME from gen WHERE name not in (SELECT name FROM mem)
  UNION ALL
  SELECT 3 as position, NAME from gre WHERE
  ORDER BY position, name
) as a

Depending on which DBMS we might need a small change for the order.

Kent
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Dynamite149Author Commented:
DBMS i'm using is
HSQLDB in OpenOffice Base

Noticed I mistyped before, gre doesn't acctually need to be included in this...

SELECT name
FROM
(
  SELECT 1 as position, NAME from mem
  UNION ALL
  SELECT 2 as position, NAME from gen WHERE name not in (SELECT name FROM mem)
  ORDER BY position, name
) as a

when I use this I come up with an error that says the following:
SQL Status: HY000
Error Code: 1000
parse error, expection 'BETWEEN' or 'IN' or 'SQL_TOKEN_LIKE'
0
 
HuyBDCommented:
Try this!

SELECT distinct NAME FROM
(
SELECT 1 as ord, NAME from mem
UNION ALL
SELECT 2 as ord, NAME from gen
UNION ALL
SELECT 3 as ord, NAME from gre
)
ORDER BY ord

Good luck!
0
 
Dynamite149Author Commented:
SQL Status: S0002
Error Code: -22

Table not found in statement [SELECT distinct NAME FROM
(
SELECT 1 as ord, NAME from mem
UNION ALL
SELECT 2 as ord, NAME from gen
UNION ALL
SELECT 3 as ord, NAME from gre
)
ORDER BY ord]
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hi Dynamite149,
SELECT distinct NAME FROM
(
SELECT 1 as ord, NAME from mem
UNION ALL
SELECT 2 as ord, NAME from gen
UNION ALL
SELECT 3 as ord, NAME from gre
)T
ORDER BY ord

Aneesh
0
 
HuyBDCommented:
I forget add prefix of sub query, so try this

SELECT distinct NAME FROM
(
SELECT 1 as ord, NAME from mem
UNION ALL
SELECT 2 as ord, NAME from gen
UNION ALL
SELECT 3 as ord, NAME from gre
) AS T
ORDER BY ord
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now