I have two SQL tables (that cannot be modified, this is a question of how to get it without like columns), and I need to find out the table type when using an inner join to list the city and states:
CREATE TABLE authors
(
au_id CHAR(3) NOT NULL,
au_fname VARCHAR(15) NOT NULL,
au_lname VARCHAR(15) NOT NULL,
phone VARCHAR(12) ,
address VARCHAR(20) ,
city VARCHAR(15) ,
state CHAR(2) ,
zip CHAR(5) ,
CONSTRAINT pk_authors PRIMARY KEY (au_id)
);
CREATE TABLE publishers
(
pub_id CHAR(3) NOT NULL,
pub_name VARCHAR(20) NOT NULL,
city VARCHAR(15) NOT NULL,
state CHAR(2) ,
country VARCHAR(15) NOT NULL,
CONSTRAINT pk_publishers PRIMARY KEY (pub_id)
);
I am attempting to use a CASE for the select to output correctly:
SELECT CASE
WHEN LCASE(SUBSTR(/*suppose id*/, 0, 1)) == 'a'
THEN 'author'
ELSE 'publisher'
END
AS type, city, state
FROM authors
INNER JOIN publishers
The tables are in a format such that the id's are different, but also hold different names:
INSERT INTO authors VALUES('A01','Sarah','Buchman','718-496-7223',
'75 West 205 St','Bronx','NY','10468');
INSERT INTO publishers VALUES('P01','Abatis Publishers','New York','NY','USA');
And I'm attempting to reach an output like so:
type | city | state
--------------------------------------------------
author | Rochester | NY
author | Syracuse | NY
publisher | New York | NY
How can I effectively make a comparison between the two tables when they do not have like columns? Can I call a field value on a table to check if it is null without throwing an SQL Exception?
As there is no column to make a comparison between the two tables i.e., nothing which can filter out the rows after Cartesian product, inner join won't server the purpose. It's more of like selecting records from two multi-sets and combining them. In T-SQL this can be achieved using Union as:
select 'authors' as [type]
, city,state
from authors
union
select 'publishers' as [type]
, city,state
from publishers