Problems in my WHERE clause :(

mturner · #1 (**permalink**) 12-09-09, 10:53

I have three tables: trips, salesmen, locations

In "trips" i have the clients name, the salesman's ID number and the location ID number.

I am querying the DB for a specific client name, and want to show the results, but with the actual names of the locations and salesmen not their ID numbers.

So here is my query that is NOT working:

SELECT trips.*, locations.*, salesmen.*
FROM trips, locations, salesmen
WHERE trips.locationid = locations.id && trips.salesmen_id = salesmen.id

This is returning a massive amount of data that repeats itself. And I happen to know, for the client I am querying their are 7 trips.

What do I do to get it to "convert" the ID numbers in trips to their full names stored in "locations" and "salesmen"?

Please help, and thank you!

r937 · #2 (**permalink**) 12-09-09, 11:10

first thing you should do is rewrite the query to use JOIN syntax

secondly, if you are "querying the DB for a specific client name" then you should put that into the WHERE clause

Code:

SELECT ...
  FROM trips
INNER
  JOIN locations
    ON locations.id = trips.locationid 
INNER
  JOIN salesmen
    ON salesmen.id = trips.salesmen_id
 WHERE trips.client_name = 'Mr. Jones'

the third step is to figure out which columns you want in the SELECT clause

mturner · #3 (**permalink**) 12-09-09, 16:20

Thank you! It worked perfectly, of course

Internet Services

Web Development

Digital Marketing

Consumer Tech