CODEWITHSUNDEEP
mysqlsql
Smith
Smith

Reputation: 5961

Multiple selection from a table

i have two tables defined like this. the table location is used to store addresses 

locations
---------
id
addr
city
zip
name

the other table which makes reference to two location address stores in the location table for each entry is this

packages
---------
id
client_id
pick_id  <-- this id is for the location address for pick
drop_id  <-- this id is for the location address for drop

i want to return

location.name, location.addr, location.city, location.id for pick
location.name, location.addr, location.city, location.id for drop

both for a given client_id

i currently have this sql

Select
  packages.status,
  locations.name,
  locations.address,
  locations.zip,
  locations.city,
  locations.id
From
  packages Inner Join
  locations On packages.drop_id = locations.id And packages.client_id = 5

but am getting empty row of result. can anyone point out my mistake?

Upvotes: 0

Views: 47

Answers (2)

Jerska
Jerska

Reputation: 12002

Shouldn't it be :

...
ON packages.drop_id = locations.id
WHERE packages.client_id = 5

? Since the packages.client_id is not used by the JOIN.

Upvotes: 2

Kickstart
Kickstart

Reputation: 21513

You only appear to be going against the locations table ones, rather for each of the drop and pick locations.

To do both use something like the following:-

Select
  packages.status,
  PickLocation.name,
  PickLocation.address,
  PickLocation.zip,
  PickLocation.city,
  PickLocation.id,
  DropLocation.name,
  DropLocation.address,
  DropLocation.zip,
  DropLocation.city,
  DropLocation.id
From packages 
INNER JOIN locations AS PickLocation ON packages.pick_id = PickLocation.id
INNER JOIN locations AS DropLocation ON packages.drop_id = DropLocation.id
WHERE packages.client_id = 5

If no records are returned then it suggests client id 5 has no packages, or either the drop or pick up location isn't on the locations table. If the latter then try using LEFT OUTER JOINs rather than INNER JOINs

Upvotes: 2

Related Questions