• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

Select from muti table statement

Hi experts,
how to make a select statement from multi table, below is the command for mysql

SELECT b.username, a.did, c.dtype, a.createdate, a.lid, b.uid FROM
link a, account b, device c WHERE a.uid=b.uid AND a.did=c.did and a.perm=1 ORDER BY a.did ASC LIMIT 1,9
0
sitijaafar
Asked:
sitijaafar
  • 5
  • 3
  • 3
  • +2
1 Solution
 
sarabhaiCommented:
Try this...

SELECT * FROM (
                        SELECT b.username, a.did, c.dtype, a.createdate, a.lid, b.uid ,ROW_NUMBER() OVER(ORDER BY a.did) as RowNum
                        FROM link a INNER JOIN account b ON a.uid=b.uid
                        INNER JOIN  device c ON a.did=c.did and a.perm=1
                    ) AS resultset
WHERE RowNum BETWEEN 1 AND 9
ORDER BY did ASC
0
 
waltersnowslinarnoldCommented:
Try the following query, I believe this will help.
SELECT b.username, a.did, c.dtype, a.createdate, a.lid, b.uid 
	FROM
link a JOIN account b 
	ON a.uid=b.uid
JOIN device c ON a.did=c.did
	WHERE 
a.perm=1 ORDER BY a.did ASC

Open in new window

0
 
spikellyCommented:
Do you want just the first nine rows? In that case run this:

SELECT TOP 9 b.username, a.did, c.dtype, a.createdate, a.lid, b.uid
FROM link a INNER JOIN account b ON a.uid=b.uid
                    INNER JOIN device c  ON a.did=c.did AND a.perm=1
ORDER BY a.did ASC
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
sameer2010Commented:
SELECT TOP 9 b.username, a.did, c.dtype, a.createdate, a.lid, b.uid FROM
link a, account b, device c WHERE a.uid=b.uid AND a.did=c.did and a.perm=1 ORDER BY a.did ASC ;
0
 
spikellyCommented:
When you select data from more than 2 tables, it's advisable to you INNER JOIN.
Why this? Simply because it result in speeding up query execution. So for better performance, use INNER JOIN than just simple WHERE clause. I rewrite the query here below:

SELECT TOP 9 b.username, a.did, c.dtype, a.createdate, a.lid, b.uid
FROM link a INNER JOIN account b ON a.uid=b.uid
                    INNER JOIN device c  ON a.did=c.did AND a.perm=1
ORDER BY a.did ASC
0
 
sameer2010Commented:
Dear Spikely, when you do not specify type of join, it defaults to INNER JOIN. There should absolutely be no different in the performance between the query with INNER JOIN and no join type specified.
0
 
spikellyCommented:
Before executing your query put:

SET STATISTICS TIME ON
SET STATISTICS IO ON
And see the input/ouptut made by the server, or the time also...

Using JOIN is best practice and increase query execution than just WHERE clauses.
0
 
sameer2010Commented:
I am really not sure why we need to do this.
http://en.wikipedia.org/wiki/Join_(SQL) - Read oprtion 2 of this

I believe, this is more of symantics than performance to user INNER JOIN keyword explicitly.
0
 
waltersnowslinarnoldCommented:
I believe the author got the solution, he dint turn up at all.
0
 
sameer2010Commented:
True :)
0
 
sitijaafarAuthor Commented:
Thanks for the wonderful feedback
0
 
sitijaafarAuthor Commented:
I'm sorry for take a time to reply.
0
 
sitijaafarAuthor Commented:
Actually I still need limit keywords, because In the program the statement is flexible to accept the limit no, because the user will choose to list how many data for example if the user choose 15 to 45 that means it will limit 15,45.  
I'm really sorry for late reply, this because I'm busy to find out the solution for another problem.  I'm quite new with programming and ms sql.
0
 
sameer2010Commented:
Try this:

SELECT username, did, dtype, createdate, lid, uid FROM (
SELECT b.username, a.did, c.dtype, a.createdate, a.lid, b.uid, 
row_number() over(order by a.did asc) as rn
FROM 
link a, account b, device c 
WHERE a.uid=b.uid AND a.did=c.did and a.perm=1) as T
WHERE T.rn between 15 and 45;

Open in new window

0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 5
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now