Lets suppose we have a database called Prints with several tables. The two tables we want to join are called ‘prints’ and ‘artists’. When we join the two we will create a new table which we will call ‘printdetails’.
The PRINTS table looks like this:
the ARTISTS table looks like this:
We want to join both tables into one by using a combination of columns from each table.
From the PRINTS table we will use id, title, price, size, description and src.
From the ARTISTS table we will use givenname, family name and aka.
Click on the SQL tab at the top of the page while you are in the main database which for us is PRINTS (Don’t confuse this with the PRINTS table) Take a look at the breadcrumb trail in the images to see what I am talking about.
We are going to create a new table called PRINTDETAILS which will be a table that has all the elements from two table in the one.
Type the following as your SQL command:
CREATE VIEW printdetails AS
SELECT prints.id,title,price,size,description,src,givenname,familyname,aka
FROM prints INNER JOIN artists ON prints.artistid = artists.id
NB: The prints.id (above) just defines that the id from the prints table should be used as both tables have an id. The capitals denotes that this is SQL command rather than the lowercase which is referring to columns and tables.
If all goes well this is what the new table looks like:
You can now call this table from your PHP script.
Altin
Nice post.How about create Table instead of View
shikamaru
Hello, I am trying to create a view that joins 2 tables “accounts” and “cases” in order to get the name of the account.
Using the cases_id field in the accounts table and the id field in the cases table
I have come up with the following, but its not working.
Could you please have a look at it.
Any help would be greatly appreciated
create view Natview_Account_Name AS
SELECT
accounts.id as accs_id,
accounts.name as account_name,
cases.account_id as cas_acc_id
FROM
accounts , cases
INNER JOIN accounts ON cases.cas_acc_id = accs_id