Dynamite149
asked on
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
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
ASKER
Not a homework assignment I assure you.... Teaching myself a bit of SQL for a database i'm trying to build..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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'
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'
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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]
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]
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
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
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
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
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