How can we retrieving data from multiple tables without using SELECT in mysql?

Replies to This Discussion

Permalink Reply by kaku on August 4, 2011 at 9:52am

create dept table(deptno,dname,loc)
create emptable(eno,ename,job,sal,deptno) using primary,refernce key


    select empno,ename,job,sal,deptno from emp where
     deptno in (select deptno from dept 
     where loc = 'CHICAGO');

data retrive from two tables with out using joins

• ▶ Reply

Permalink Reply by Arvi on August 4, 2011 at 11:56am

In this query we can say that we are using two different tables to retrieve the data; but we are retrieving the data (empno, ename,job,sal,deptno) only from one table viz emp and not from dept. Let me know your thoughts.

 

Regards,

Arvi, QTPhelp.com

• ▶ Reply

Permalink Reply by Arvi on August 4, 2011 at 5:51pm

you can try something like this though...

 

Select emp.ename, dept.dname from emp, dept where emp.eno in (Select eno from emp)

 

ahh.. I guess this is the answer to the original question. I tried and it works.

• ▶ Reply

Permalink Reply by Hitesh Shah on December 2, 2011 at 1:54pm

Hi,

nice explain. Between join, sub query & nested query, who will give fast result?

• ▶ Reply

Permalink Reply by Mythreyi on August 4, 2011 at 5:11pm

Hi,

 

Thanks all of you.

 

@Arvi,

I have one more question, We can retrieve data from 2 tables using alias/nested queries & Joins.

Both are retrieving same data then what is the difference between alias/nested queries & Joins?

 

• ▶ Reply

Permalink Reply by neha bhardwaj on August 4, 2011 at 5:19pm

gud questions plz arvi i need answer of this question too

 

• ▶ Reply

Permalink Reply by Arvi on August 4, 2011 at 5:28pm

Nested : Nested query is using the results of a subquery in the main query (A Query inside a query)

Alias:http://www.w3schools.com/sql/sql_alias.asp

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

 

Regards,

Arvi, 

Check out how to Connect to database using QTP

• ▶ Reply

Permalink Reply by Mythreyi on August 4, 2011 at 6:01pm

 

With Alias - we can alias a name to a column or table and retrieve the data from 2 tables.

Joins- We can retrieve data from more than 2 tables.

 

With Alias also we can retrieve the data then why we use Joins? pls.. explain clearly. Bcoz this question is asked in one interview.

• ▶ Reply

Permalink Reply by Arvi on August 4, 2011 at 6:04pm

Using Alias - Implicit joins

Using Joins - Explicit joins

Both are joins only, but different join notions. The only way to retrieve from 2 tables is using sub queries. I have specified  the query above.

• ▶ Reply

Permalink Reply by Master117 on December 27, 2011 at 5:47pm

mysql> SELECT * FROM officeTable WHERE Empid in(SELECT Empid FROM  employee); is this what you are looking for?

• ▶ Reply

• ‹ Previous
• 1
• 2
• Next ›
• Page