The W
Views: 99038476
Main | FAQ | Search: Y! / G | Calendar | Color chart | Log in for more!
19.9.14 1105
The W - Internet & Computers - SQL question (simple, maybe?)
This thread has 2 referrals leading to it
Register and log in to post!
Thread rated: 9.15
Pages: 1
(238 newer) Next thread | Previous thread
User
Post (3 total)
bradbice
Chorizo








Since: 2.1.02
From: MI

Since last post: 574 days
Last activity: 1 day
#1 Posted on
I have two tables I am pulling data from with one query:

SELECT studentid, firstname, lastname, classname FROM students, classes WHERE students.classid = classes.classid ORDER BY lastname ASC

Tables are:

-students-
studentid
firstname
lastname
classid

-classes-
classid
classname

The above query (with help from PHP) prints out a table of students first and last names, replacing the classid with classname.

However, when I delete a record from the 'classes' table, it causes any students with that particular classid to not show up, since nothing matches between the students and classes tables as far as classid.

So what is the best way to display all records in the 'students' table, while still replacing the classid with the classname, and also displaying those that have a deleted/unassociated classid?

Thanks for any help.



2009 NBA Playoffs bracket
Promote this thread!
thecubsfan
Scrapple
Moderator








Since: 10.12.01
From: Aurora, IL

Since last post: 7 days
Last activity: 33 min.
#2 Posted on | Instant Rating: 10.00
This is a one of those LEFT JOIN things, I think. I hate doing LEFT JOIN (outside of getting Access to work it for me.)

    Originally posted by w3schools

    The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).

    SQL LEFT JOIN Syntax

    SELECT column_name(s)
    FROM table_name1
    LEFT JOIN table_name2
    ON table_name1.column_name=table_name2.column_name

    PS: In some databases LEFT JOIN is called LEFT OUTER JOIN.



With Left Join, it's the first table after FROM that you get all the records from. You get the matching records from LEFT JOIN. (There's a right join that works the opposite way.)

There's actually an example on that page which pretty closely matches what you're doing, but my guess is

SELECT studentid, firstname, lastname, classname FROM students LEFT JOIN classes ON students.classid = classes.classid ORDER BY lastname ASC



thecubsfan.com - luchablog
bradbice
Chorizo








Since: 2.1.02
From: MI

Since last post: 574 days
Last activity: 1 day
#3 Posted on
AWESOME. That worked. You are the fan. Thank you so much.



2009 NBA Playoffs bracket
Thread rated: 9.15
Pages: 1
Thread ahead: Anyone having issues with Hotmail?
Next thread: Stupid Browsing Question
Previous thread: Behold: the newest iPod Shuffle!
(238 newer) Next thread | Previous thread
I have a Jabra one I've been using for a few years. I don't know if it is a currently available model but I bought it for an Avaya phone, and it works just fine with our newer Polycom phones we picked up for Lync.
- Guru Zim, need advice (2013)
The W - Internet & Computers - SQL question (simple, maybe?)Register and log in to post!

The W™ message board

ZimBoard
©2001-2014 Brothers Zim

This old hunk of junk rendered your page in 0.06 seconds.