• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

Problem with select statement

I have a select statement and each time I try to select I get differenct errors.

select org.name, loc.address1, loc.address2, loc.city, loc.state, loc.zip,phones.phone from locations as loc, organizations as org , phones
inner join organizationlocations as orgloc on org.organizationid = orgloc.organizationid
inner join locations as loc1 on orgloc.locationsid = loc1.locationid

The one I am getting at the moment is

 Line 1
The column prefix 'org' does not match with a table name or alias name used in the query.

Thanks
0
running32
Asked:
running32
  • 4
  • 2
  • 2
3 Solutions
 
ABaruhCommented:
select org.name, loc.address1, loc.address2, loc.city, loc.state, loc.zip,phones.phone from locations loc, organizations org , phones
inner join organizationlocations orgloc on org.organizationid = orgloc.organizationid
inner join locations loc1 on orgloc.locationsid = loc1.locationid
0
 
ABaruhCommented:
woah, you are joining locations back to itself?  why?  your query should be:

select org.name, loc.address1, loc.address2, loc.city, loc.state, loc.zip,phones.phone from locations loc, organizations org , phones
inner join organizationlocations orgloc on org.organizationid = orgloc.organizationid
and orgloc.locationsid = loc1.locationid
0
 
Anthony PerkinsCommented:
Something like this perhaps:

select      org.name,
            loc.address1,
            loc.address2,
            loc.city,
            loc.state,
            loc.zip,
            phones.phone
from      locations as loc
            Inner Join organizations as org On ??????
            Inner Join phones On ?????
            inner join organizationlocations as orgloc on org.organizationid = orgloc.organizationid
            inner join locations as loc1 on orgloc.locationsid = loc1.locationid
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
running32Author Commented:
I guess my query does not work at all.

What I need is the name, address from the tables organization and locations.

These tables are linked by a locationid.  organization table has organizationid and organizationlocations table has organizationid and locationid.  Then locations table has locations id.  I need to join up the 3 tables so that I can pull the information.  

Thanks for your help
0
 
ABaruhCommented:
select
     org.name
     , loc.address1
     , loc.address2
     , loc.city
     , loc.state
     , loc.zip
     , phones.phone
from
     locations loc
inner join
     organizationlocations orgloc on
          loc.locationid = orgloc.locationid
inner join
     organizationlocations orgloc on
          org.organizationid = orgloc.organizationid
inner join
     phones on
          loc.locationid = phones.locationid
0
 
ABaruhCommented:
woops...

select
     org.name
     , loc.address1
     , loc.address2
     , loc.city
     , loc.state
     , loc.zip
     , phones.phone
from
     locations loc
inner join
     organizationlocations orgloc on
          loc.locationid = orgloc.locationid
inner join
     organizations org on
          org.organizationid = orgloc.organizationid
inner join
     phones on
          loc.locationid = phones.locationid
0
 
Anthony PerkinsCommented:
You did not say anything about Phones

select     org.name,
          loc.address1,
          loc.address2,
          loc.city,
          loc.state,
          loc.zip,
          phones.phone
from     locations loc
          inner join organizationlocations orgloc on loc.locationid = orgloc.locationid
          Inner Join organizations org On orgloc.organizationid = org.organizationid
          Inner Join phones On ?????
0
 
running32Author Commented:
thanks guys for answering back so quickly I really appricate it.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now