473,837 Members | 1,424 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

best way to show a mysql join

I have the following tables:

students
id, name,....

lessons
id,description, ....

studentslessons
id,studentid,le ssonid,grade

I want to list the lessons of student with name="John" for example.

The one way to do it is :
select id from students where name="John" and grab the id. then select *
from studentslessons where studentid=id;

the other is
select * from students,studen tslessons where students.name=" john" AND
students.id=stu dentslessons.st udentid
Which is more efficient when using php4 and mysql 4.1? I'm using a
rather old computer Pentium 800MHz.

thanks
Feb 1 '06 #1
2 1443
G0ng wrote:
I have the following tables:

students
id, name,....

lessons
id,description, ....

studentslessons
id,studentid,le ssonid,grade

I want to list the lessons of student with name="John" for example.

The one way to do it is :
select id from students where name="John" and grab the id. then select *
from studentslessons where studentid=id;

the other is
select * from students,studen tslessons where students.name=" john" AND
students.id=stu dentslessons.st udentid
Which is more efficient when using php4 and mysql 4.1? I'm using a
rather old computer Pentium 800MHz.

thanks


Hi

I fail to see how this is related to PHP. :P

But you can bet the second one is faster because it is only 1 query.

Many databases will first make an executionplan, then execute it.

The quality of the executionplan depends on the database, and possibly
'hints' it received.

In general: Less queries are faster then many queries, and most databases
make good executionplans.

Hope that helps.

Regards,
Erwin Moller
Feb 1 '06 #2
G0ng wrote:
I have the following tables:

students
id, name,....

lessons
id,description, ....

studentslessons
id,studentid,le ssonid,grade

I want to list the lessons of student with name="John" for example.

The one way to do it is :
select id from students where name="John" and grab the id. then select *
from studentslessons where studentid=id;

the other is
select * from students,studen tslessons where students.name=" john" AND
students.id=stu dentslessons.st udentid
IME, I find queries that actually use JOIN to be quite a bit quicker. In
your case, you should try something similar to the following:

SELECT students.name, lessons.descrip tion, studentslessons .grade
FROM students
INNER JOIN lessons
INNER JOIN studentslessons ON studentslessons .studentid = students.id
ON lessons.id = studentslessons .lessonid
WHERE students.name = 'John'
Which is more efficient when using php4 and mysql 4.1? I'm using a
rather old computer Pentium 800MHz.


With php, the fewer queries you issue, the more efficient the code will
likely be. That way all the work is being done on the mysql server
rather than the web server. The only thing you have to do then is
optimize the queries to be faster.

--
Justin Koivisto, ZCE - ju****@koivi.co m
http://koivi.com
Feb 1 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
5565
by: aaron | last post by:
I have a question about (i think) joining. If I have a table in a database that has this info: key - name - favorite 1 - john - 2 2 - judy - 3 3 - joe - 1 the favorite icecream table is this:
6
3080
by: Xenophobe | last post by:
I know this isn't a MySQL forum, but my question is related to a PHP project. I have two tables. table1 table2 "table1" contains 2 columns, ID and FirstName:
23
2569
by: phpfrizzle | last post by:
Hi there, I have a site with products on it. The site has a mysql backend. All products belong to certain series (table series). There can be up to 4 different products (table products) (categories 1-4) in 1 series. Each product has a defined 'series ID' which tells us
0
2368
by: Soefara | last post by:
Dear Sirs, I am experiencing strange results when trying to optimize a LEFT JOIN on 3 tables using MySQL. Given 3 tables A, B, C such as the following: create table A ( uniqueId int not null default 0 auto_increment, a1 varchar(64) not null default '',
0
1388
by: Phil Powell | last post by:
I have a rather complicated query with a combination of LEFT JOINs and two MATCHES where the first match is non-boolean to get the accurate score, the second to search as boolean: SELECT image.id, image.image_name, (MATCH (image_name, image_alt, image_location_city, image_location_state, image_location_country) AGAINST ('test')
11
9293
by: DrUg13 | last post by:
In java, this seems so easy. You need a new object Object test = new Object() gives me exactly what I want. could someone please help me understand the different ways to do the same thing in C++. I find my self sometimes, trying Object app = Object(); Object *app = Object(); Object app = new Object();
9
1785
by: Luke Vogel | last post by:
Hi all. This is a bit of a newbie type question. I am trying to figure out what is the best way to connect to a database; ado.net, odic others? I've found a couple of examples that show you how to connect to the "Northwind" database, using oledb??
0
2295
by: yeahuh | last post by:
Quick and dirty version. Godaddy server using MySQL 4.0.24 I’m trying a left join to obtain id’s in table A(cars) that are NOT in car_id in table B(newspaper): *This is a cut down version to simplify testing. Full version is posted towards the end. SELECT C.id FROM cars C LEFT OUTER JOIN newspaper N USING (C.id=N.car_id) WHERE N.car_id IS NOT NULL;
0
2169
by: jllanten | last post by:
I will appreciate any help you can provide me. In the company where i work we have a project which creates about 4-5M records daily of stats. We're currently storing this data in a db named 'summary' which contains all the stats data for up to 15 days. After that we begin to move the data in a daily basis to a single 'archived like' unique table in another fast performance mysql instance. Why we do this ? to respect the 80%-20% rule: 80% of...
0
9682
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10871
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10268
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9396
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7806
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5668
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5846
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4039
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3123
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.