topr8
msg:4107903 | 11:37 pm on Mar 31, 2010 (gmt 0) |
SELECT companyname,companylogo,url FROM friends,employees,companies
WHERE who=$selfid AND userid=whom AND companies.companyid=employees.companyid
|
syber
msg:4107952 | 1:23 am on Apr 1, 2010 (gmt 0) |
or this:
SELECT companyname,companylogo,url
FROM companies
WHERE companyid = (SELECT companyid
FROM employees
WHERE userid = (SELECT whom
FROM friends
WHERE who ='$selfid')
|
meee
msg:4108162 | 10:18 am on Apr 1, 2010 (gmt 0) |
Thank you! I found another option is also INNER JOIN. But now I am confused what is the best option.
a)
SELECT companyname,companylogo,url FROM friends,employees,companies
WHERE who=$selfid AND userid=whom AND companies.companyid=employees.companyid
b)
SELECT companyname,companylogo,url
FROM companies
WHERE companyid = (SELECT companyid
FROM employees
WHERE userid = (SELECT whom
FROM friends
WHERE who ='$selfid')
or c)
SELECT companyname, companylogo, url FROM companies
INNER JOIN employees ON employees.companyid = companies.companuid -- which have employess
INNER JOIN friends ON friends.whom = employees AND friends.who='$selfid'
|
bizminder
msg:4111273 | 9:24 am on Apr 7, 2010 (gmt 0) |
That was very helpful. SELECT FROM WHERE is the standard for any operation. However could anybody brief me about how inner joins work?
|
LifeinAsia
msg:4111506 | 3:55 pm on Apr 7, 2010 (gmt 0) |
| However could anybody brief me about how inner joins work? |
|
Do you remember Venn diagrams from math? It's the same principle- you are comparing DB tables with JOINS (SQL) instead of sets with intersections (Venn diagrams).
The following 2 queries are identical:
SELECT Tablea.*, Tableb.*
FROM Tablea,Tableb
WHERE Tablea.idcolumn=Tableb.idcolumn
SELECT Tablea.*, Tableb.*
FROM Tablea INNER JOIN Tableb ON Tablea.idcolumn=Tableb.idcolumn
|
topr8
msg:4111511 | 4:03 pm on Apr 7, 2010 (gmt 0) |
| But now I am confused what is the best option. |
|
generally (and it is only a generalisation) using subqueries will be slower
a subquery is something like:
WHERE userid = (SELECT whom FROM friends WHERE who ='$selfid')
|
Demaestro
msg:4111523 | 4:16 pm on Apr 7, 2010 (gmt 0) |
| But now I am confused what is the best option. |
|
I will also add that using a left join will return null results and inner join will not.
|
syber
msg:4111659 | 7:43 pm on Apr 7, 2010 (gmt 0) |
I disagree with the statement that subqueries will generally be slower. Joins can be very expensive.
In the example given:
WHERE userid = (SELECT whom FROM friends WHERE who ='$selfid')
should be much faster than a join, as the subquery will only return one value for the outer query to check against. If the subquery returned a value for whom as 82, then it would be the same as saying: WHERE userid = 82
|
topr8
msg:4111765 | 12:58 am on Apr 8, 2010 (gmt 0) |
>>If the subquery returned a value for whom as 82, then it would be the same as saying: WHERE userid = 82
no, not really. as in yes of course it would be like saying: WHERE userid = 82
however there is an expense in getting the value 82 in the first place, the table needs to be scanned, indexes looked up or whatever.
also it is my understanding (and i'm happy to be corrected) that MySQL does not process subqueries very efficiently - unlike sqlserver which is very efficient - after all as far as i know subqueries were only introduced from version MySQL 4.1
anyway i guess it depends on the data and the way the tables are indexed, for the future reference of anyone stumbling onto this thread running EXPLAIN for both variants will tell you which is more efficient!
|
meee
msg:4115987 | 9:09 pm on Apr 14, 2010 (gmt 0) |
I have now another but similar problem. The situation is very similar but I need to get information from two tables. Is also for this any similar solution or do I need now to do actually a new query for each user?
table employees
id | userid | companyid | position
table users
userid | firstname | lastname | age | email | password ...
[php]
$employees=mysql_query("SELECT userid, position FROM employees WHERE companyid='$companyid' LIMIT $paginationLimit ");
while($rowemployees=mysql_fetch_assoc($employees){
$userid=$rowemployees[userid];
$position[]=$rowemployees[position];
$employeeDetails=mysql_query("SELECT * FROM users WHERE userid='$userid'");
$rowEmployeeDetails=mysql_fetch_assoc($employeeDetails)
$firstname[]=$rowEmployeeDetails[firstname];
$lastname[]=$rowEmployeeDetails[lastname];
$age[]=$rowEmployeeDetails[age];
$position[]=$rowEmployeeDetails[position];
}
[/php]
Thanks again!
|
propertyncr
msg:4116159 | 7:04 am on Apr 15, 2010 (gmt 0) |
Dear you just try w3c school for it
|
coopster
msg:4116269 | 11:21 am on Apr 15, 2010 (gmt 0) |
| also it is my understanding (and i'm happy to be corrected) that MySQL does not process subqueries very efficiently - unlike sqlserver which is very efficient - after all as far as i know subqueries were only introduced from version MySQL 4.1 |
|
FWIW, I've been running subqueries with MySQL since it was in release candidate stages, prior to general availability in 2004 [mysql.com]. The only performance issues I have ever had with MySQL subqueries are the very same issues I've had with other RDBMS, including SQL server -- missing indexes. You plan and write the application, building indexes when you first create the tables. Then you end up writing a query that hits the table from a different angle than you originally planned. I've found that logging queries during development and testing initially, and then analyzing them during quality assurance testing of an application, to be extremely beneficial. Like you said, topr8, be certain to use the query analyzer provided by the RDBMS to find your performance bottlenecks.
|
coopster
msg:4116270 | 11:25 am on Apr 15, 2010 (gmt 0) |
| The situation is very similar but I need to get information from two tables. |
|
meee, read about the JOIN options that the others discussed so far.
|
|
