Create a view on two tables

Forum Moderators: open

Message Too Old, No Replies

Create a view on two tables

possible?

RonPK

9:32 am on Feb 19, 2008 (gmt 0)

I have two MySQL tables and would like to have a view so that I can later address them in a single query.

clientsA (clientID int, projectID tinyint) 
clientsB (clientID int, projectID tinyint) 
   
CREATE VIEW v_clients (clientID, projectID) 
AS SELECT ?.?.?.?

I tried some joins but they all resulted in far more records in the view than the sum of the two tables would suggest. (I'm new to views, so bare with me..)

[edited by: RonPK at 9:35 am (utc) on Feb. 19, 2008]

syber

1:16 pm on Feb 19, 2008 (gmt 0)

Looks like you need a UNION here, not a JOIN:

CREATE VIEW v_clients (clientID, projectID)
AS
SELECT * FROM clientsA
UNION ALL
SELECT * FROM clientsB

RonPK

4:58 pm on Feb 19, 2008 (gmt 0)

Thanks syber, that's it!