joining three tables in an SQL query / Computer Science / Forums

Welcome

Online

Members Birthdays:

All today's birthdays', congrats!
GreySoul (45)
GluD (35)
northern_lightning (41)
w1vlf (67)
louis.haeb (30)

Next birthdays
06/03 mileswaldron (59)
06/04 muze801 (33)
06/05 HVgeek (33)

Contact

Support 4hv.org!

Donate:
4hv.org is hosted on a dedicated server. Unfortunately, this server costs and we rely on the help of site members to keep 4hv.org running. Please consider donating. We will place your name on the thanks list and you'll be helping to keep 4hv.org alive and free for everyone. Members whose names appear in red bold have donated recently. Green bold denotes those who have recently donated to keep the server carbon neutral.
Special Thanks To:

Aaron Holmes
Aaron Wheeler
Adam Horden
Alan Scrimgeour
Andre
Andrew Haynes
Anonymous000
asabase
Austin Weil
barney
Barry
Bert Hickman
Bill Kukowski
Blitzorn
Brandon Paradelas
Bruce Bowling
BubeeMike
Byong Park
Cesiumsponge
Chris F.
Chris Hooper
Corey Worthington
Derek Woodroffe
Dalus
Dan Strother
Daniel Davis
Daniel Uhrenholt
datasheetarchive
Dave Billington
Dave Marshall
David F.
Dennis Rogers
drelectrix
Dr. John Gudenas
Dr. Spark
E.TexasTesla
eastvoltresearch
Eirik Taylor
Erik Dyakov
Erlend^SE
Finn Hammer
Firebug24k
GalliumMan
Gary Peterson
George Slade
GhostNull
Gordon Mcknight
Graham Armitage
Grant
GreySoul
Henry H
IamSmooth
In memory of Leo Powning
Jacob Cash
James Howells
James Pawson
Jeff Greenfield
Jeff Thomas
Jesse Frost
Jim Mitchell
jlr134
Joe Mastroianni
John Forcina
John Oberg
John Willcutt
Jon Newcomb
klugesmith
Leslie Wright
Lutz Hoffman
Mads Barnkob
Martin King
Mats Karlsson
Matt Gibson
Matthew Guidry
mbd
Michael D'Angelo
Mikkel
mileswaldron
mister_rf
Neil Foster
Nick de Smith
Nick Soroka
nicklenorp
Nik
Norman Stanley
Patrick Coleman
Paul Brodie
Paul Jordan
Paul Montgomery
Ped
Peter Krogen
Peter Terren
PhilGood
Richard Feldman
Robert Bush
Royce Bailey
Scott Fusare
Scott Newman
smiffy
Stella
Steven Busic
Steve Conner
Steve Jones
Steve Ward
Sulaiman
Thomas Coyle
Thomas A. Wallace
Thomas W
Timo
Torch
Ulf Jonsson
vasil
Vaxian
vladi mazzilli
wastehl
Weston
William Kim
William N.
William Stehl
Wesley Venis

The aforementioned have contributed financially to the continuing triumph of 4hv.org. They are deserving of my most heartfelt thanks.

Forums

4hv.org :: Forums :: Computer Science

« Previous topic | Next topic »

joining three tables in an SQL query

Move Thread

LAN_403

ragnar

Mon Sept 21 2009, 09:26AM

Registered Member #63 Joined: Thu Feb 09 2006, 06:18AM
Location:
Posts: 1425

Hi all,

I'm trying to achieve what I think are two JOINs using the following tables:

TABLE messages
+--------+----------+---------+
| client | customer | message |
| 1      | a        | foo     |
| 2      | b        | bar     |
| 3      | c        | baz     |
+--------+----------+---------+

TABLE clients
+----+------+
| id | name |
| 1  | matt |
| 2  | john |
| 3  | mark |
+----+------+

TABLE customers
+----+------+
| id | name |
| a  | jane |
| b  | gina |
| c  | jess |
+----+------+

My desired results are:

RESULT
+-------------+---------------+---------+
| client.name | customer.name | message |
| matt        | jane          | foo     |
| john        | gina          | bar     |
| mark        | jess          | baz     |
+-------------+---------------+---------+

I've been messing around with queries for a while now, and I'm getting close with:

SELECT clients.name,customers.name,messages.message FROM messages
JOIN clients ON messages.client=clients.id 
JOIN customers ON messages.customer=customers.id

However the result I am getting omits all message rows with a blank client, and the entire customer column remains empty. I know it's doing exactly what I've asked, but can anyone guide me to exactly what I need?

Myke

Mon Sept 21 2009, 10:13PM

Registered Member #540 Joined: Mon Feb 19 2007, 07:49PM
Location: MIT
Posts: 969

I learned another way to do something similar to joins but I'm not sure if it will work completely.

No harm in trying. I'm going to just guess at the ids of the tables.
Try:

SELECT client.name,customer.name,message FROM messages,clients,customers WHERE idclient = idclients AND idcustomer = idcustomers

Did you fill up all the tables including the look up table with information?
I'll test out the setup and see what happens.

EDIT: tested it out and it works fine

Here it is in mysql workbench

I filled the tables out like you said you did and here is the code used to get the output you discribed:

SELECT name_client,name_customer,message FROM clients,customers,message WHERE clients_idclients=idclients AND customers_idcustomers=idcustomers

Oh, I see what you did. The messages table doesn't contain the names of the people that you are trying to look up. You have to have each table listed after the FROM.

ragnar

Wed Sept 23 2009, 02:17PM

Registered Member #63 Joined: Thu Feb 09 2006, 06:18AM
Location:
Posts: 1425

Hi Myke,

I can't replicate your results: I just get empty tables. I do appreciate you setting up the tests to try to work out a solution, thanks.

To better word my problem, I really just want to dump the MESSAGES table, but replace the client/customer foreign keys with the respective client/customer names.

For now I am doing extra queries as PHP handles each row, but doing it with a single query would solve my performance problem

Myke

Wed Sept 23 2009, 09:49PM

Registered Member #540 Joined: Mon Feb 19 2007, 07:49PM
Location: MIT
Posts: 969

Oh... You may want to look into left joins then. I don't know much about them besides they can have blanks in the right (I think).

Moderator(s): Chris Russell, Noelle, Alex, Tesladownunder, Dave Marshall, Dave Billington, Bjørn, Steve Conner, Wolfram, Kizmo, Mads Barnkob

Legal Information