Hi Ken,

I will give you an example of a database with 3 tables namely tableOne, tableTwo and TableThree

Lets assume tableOne has three fields namely tb1Col1, tb1Col2 and tb1Col3

and tableTwo has three fields namely tb2Col1, tb2Col2 and tb2Col3

and tableThree has three field namely tb3Col1, tb3Col2 and tb3Col3.

To do a select query of all fields in the three tables you would write a select query that looks as follows;

select a.tb1Col1, a.tb1Col2, a.tb1Col3, b.tb2Col1, b.tb2Col2, b.tb2Col3, c.tb3Col1, c.tb3Col2, c.tb3Col3

From tableOne a, tableTwo b, tableThree c

but then this one will give you a cartesian product if you don't have what you are checking against.

To avoid it you need to select where e.g. two or more columns have a relationship you can check against. Let's say if in tableOne the column tb1Col1 is the primary key column of that table and tb2Col2 is the foreign key column in tableTwo linked to tableOne and tableThree is linked to tableOne via foreign key in column tb3Col2 then your SQL query would be as follows;

Select a.tb1Col1, a.tb1Col2, a.tb1Col3, b.tb2Col1, b.tb2Col2, b.tb2Col3, c.tb3Col1, c.tb3Col2, c.tb3Col3

From tableOne a, tableTwo b, tableThree c

Where a.tb1Col1 = b.tb2Col2 and

a.tb1Col1 = c.tb3Col2

Hope this helps though there are lots of materials online for the same.

Regards,

Joseph

(I'm pointing to Oracle as I saw the SQL*Plus tag)

As for the material on the SELECT, see http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm .

Usually the relationships mentioned by Joseph above are reflected in the fact that two tables show the same column names, in that case, the NATURAL JOIN clause of the SELECT statement can shorten and cleanse the syntax.

Also, if that query is really complex and you need its results in a sort of "table" of its own, consider creating a view. Refer to the documentation http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8004.htm

you can make JOIN tables with SQL query and operate you sub query or correlation  query  then optimise the query

Use Join statements based on primary and foreign keys to retrieve data.

Using Left Inner, Outer Joins is the simplest and best performance method

You should certainly use the chapter on joins. MySQL documentation contains a long description of all types of joins including examples. You should do the examples on your sample database, the differences between all kinds of joins are not visible just from looking into the manual, you have to try yourself. To make it clear, a.tb1Col1 = c.tb3Col2 is not a NATURAL JOIN, it is LEFT JOIN ON a.tb1Col1 = c.tb3Col2. Keys are not needed for joins. However, PRIMARY KEY or UNIQUE will ensure that you have at most one row with such a vallue. If the tables are indexed using all columns that you need for search and relations, your queries will be much faster. Foreign keys do not server primarily for queries, they help because they must be UNIQUE but their role is to help you to maintain database integrity.

Hi Ken,

We can best help if you'll output the DDL schema (script it) and define the query requirements needed. There's much to consider, especially if the database isn't normalized or lacks data-integrity.

Les

first thing we should know , How your trying to retrieve ? in a single query u have more than 20 tables. ?   

if this is the case use joins, and sub queries.  

retrieving data from database is based on the table structure and the way we normalize the tables. so please check the tables and the relation ships between those.

we can also use views. for faster retrieving and we can also use indexes.  

Thanks everyone. Your contributions have jerked back my memory and i was able to accomplish the task using inner join.

CREATE TABLE TABLENAME (

ID INT NOT NULL,

NAME VARCHAR(25),

QUALIFICATION VARCHAR(25),

CGPA FLOAT,

PRIMARY KEY (ID)

);

INSERT STATEMENT

INSERT INTO TABLENAME

VALUES('10','XYZ','BS','3.5');

AND FINALLY SELECT STATEMENT

SELECT * FROM TABLENAME

THANKS.

use the join  with sub query, correlated query to resolve your problem

You may use join statement and make sure you call the common columns as foreign key to get data from different tables for example visit

http://www.w3schools.com/sql/sql_join.asp