The Web Design Group

... Making the Web accessible to all.

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Creating a view from multiple tables
tudsy
post Aug 5 2018, 11:32 PM
Post #1


Advanced Member
****

Group: Members
Posts: 148
Joined: 30-September 14
Member No.: 21,611



Hi

I have 4 tables, CustomersArt,Customers,subscribers and emailcomm.

My question is, how do i create a view from these 4 tables with the column email ?

I have done a view from one table (see below):

$query = "CREATE VIEW ecovib2d AS SELECT email FROM CustomersArt";


Any help will be appreciated.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Aug 6 2018, 01:42 PM
Post #2


Programming Fanatic
********

Group: Members
Posts: 1,514
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



You need to look in to INNER JOIN or OUTER JOIN to link the tables. You don't provide enough detail for me to suggest anything else.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
tudsy
post Aug 6 2018, 01:57 PM
Post #3


Advanced Member
****

Group: Members
Posts: 148
Joined: 30-September 14
Member No.: 21,611



QUOTE(CharlesEF @ Aug 7 2018, 04:12 AM) *

You need to look in to INNER JOIN or OUTER JOIN to link the tables. You don't provide enough detail for me to suggest anything else.

Hi

Thanks for that.

More detail.

Here are the tables with the column names:


CustomersArt(Itemid,email,Graphic,name,Price,terms,Age,Date);
Customers(Itemid,email,name,Price,terms,Age,Date,Softbought);
emailcomm(Itemid,Age,name,email,Date);
subscribers(itemid,username,email,ConfirmationCode,Confirmation,Date);

Thanks.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Aug 6 2018, 02:07 PM
Post #4


Programming Fanatic
********

Group: Members
Posts: 1,514
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



You still don't tell me what columns you want from each table. Anyway, you should post your own code that shows your attempt and then I can help. You won't learn anything if I write it for you.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
tudsy
post Aug 6 2018, 11:30 PM
Post #5


Advanced Member
****

Group: Members
Posts: 148
Joined: 30-September 14
Member No.: 21,611



QUOTE(CharlesEF @ Aug 7 2018, 04:37 AM) *

You still don't tell me what columns you want from each table. Anyway, you should post your own code that shows your attempt and then I can help. You won't learn anything if I write it for you.



Thanks for that.

The user or visitor enters an email address and displays the entire 4 tables according to the email address.

Thanks.

User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
tudsy
post Aug 10 2018, 03:55 AM
Post #6


Advanced Member
****

Group: Members
Posts: 148
Joined: 30-September 14
Member No.: 21,611



Hi

A solution:

“CREATE VIEW ecovib2d AS
SELECT CustomersArt.email,Customers.email,emailcomm.email,subscribers.email”;


Is this correct?

Thanks.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Aug 10 2018, 12:13 PM
Post #7


Programming Fanatic
********

Group: Members
Posts: 1,514
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



No. You're not joining any tables. Here is an example, taken from here:
CODE
CREATE VIEW GiftsList
AS
SELECT  b.name user_from,
        c.name user_to,
        d.name gift_name,
        d.price gift_price
FROM    gift a
        INNER JOIN users b
            ON a.user_from = b.id
        INNER JOIN users c
            ON a.user_from = c.id
        INNER JOIN items d
            ON a.item = d.id
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Aug 10 2018, 03:31 PM
Post #8


Programming Fanatic
********

Group: Members
Posts: 1,514
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



You should start by joining just 1 table. I haven't tried to explain INNER JOIN because there are hundreds of examples on the internet.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Aug 11 2018, 12:21 PM
Post #9


Programming Fanatic
********

Group: Members
Posts: 1,514
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



Ok, here is a hint. Since you never said which columns you want I have included all of them from Customers.
CODE
$query = "CREATE VIEW ecovib2d AS SELECT a.email, b.name, b.Price, b.terms, b.Age, b.Date, b.Softbought FROM CustomersArt a
INNER JOIN Customers b ON a.email = b.email";
You still need to add columns from the other 2 tables but this should get you started. You may not need INNER JOIN, maybe a regular JOIN will work?
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
tudsy
post Aug 11 2018, 10:38 PM
Post #10


Advanced Member
****

Group: Members
Posts: 148
Joined: 30-September 14
Member No.: 21,611



QUOTE(CharlesEF @ Aug 12 2018, 02:51 AM) *

Ok, here is a hint. Since you never said which columns you want I have included all of them from Customers.
CODE
$query = "CREATE VIEW ecovib2d AS SELECT a.email, b.name, b.Price, b.terms, b.Age, b.Date, b.Softbought FROM CustomersArt a
INNER JOIN Customers b ON a.email = b.email";
You still need to add columns from the other 2 tables but this should get you started. You may not need INNER JOIN, maybe a regular JOIN will work?


Thanks for that.

Maybe this would work ?



$query = "CREATE VIEW ecovib2d AS
SELECT
CustomersArt.*,
Customers.*,
emailcomm.*,
subscribers.*
FROM CustomersArt
JOIN Customers
ON Customers.* = CustomersArt.*
JOIN emailcomm
ON emailcomm.* = Customers.*
JOIN subscribers
ON subscribers.* = emailcomm.*
WHERE email = $Email";

Thanks.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Aug 12 2018, 12:16 AM
Post #11


Programming Fanatic
********

Group: Members
Posts: 1,514
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



I don't think so, you need to change the ON part. You must join based on the email address. Also, some of your tables have the same column name. You might have to use an alias for those columns (look in to AS). In other words, you might have to specify column names and not use the * character. And you might want to use table names only once. In my post a = table CustomersArt while b = Customers. I've never created a VIEW so you may have to do some testing.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post

Reply to this topicStart new topic
2 User(s) are reading this topic (2 Guests and 0 Anonymous Users)
0 Members:

 



- Lo-Fi Version Time is now: 14th August 2018 - 06:14 AM