February 28, 2011 at 7:18 pm
Here is how the two tables are setup:
Table1 (master)
ID
Location
LocationID
Lat
Long
Map
Table2(child)
ID
LocationID
Map
SQL query
SELECT
Table1.ID,
Table1.LocationID,
Table1.Lat,
Table1.Long,
Table2.[Map]
FROM Table1
LEFT OUTER JOIN Table2 ON Table1.LocationID = Table2.LocationID
i know this querry is worng, but what is wrong with it if the SQL returns no errors.
What my ultimate goal is to display all Locations that belong to a specific LocationID
Please guys help to the new guy 🙂
Thanks
March 1, 2011 at 3:37 am
This was removed by the editor as SPAM
March 1, 2011 at 6:18 am
xeops (2/28/2011)
Here is how the two tables are setup:Table1 (master)
ID
Location
LocationID
Lat
Long
Map
Table2(child)
ID
LocationID
Map
SQL query
SELECT
Table1.ID,
Table1.LocationID,
Table1.Lat,
Table1.Long,
Table2.[Map]
FROM Table1
LEFT OUTER JOIN Table2 ON Table1.LocationID = Table2.LocationID
i know this querry is worng, but what is wrong with it if the SQL returns no errors.
What my ultimate goal is to display all Locations that belong to a specific LocationID
Please guys help to the new guy 🙂
Thanks
SQL doesn't necessarily give an error when it gives you different information than you were expecting. So "wrong" can mean "error", or it can mean "not what I wanted".
Based on the table definitions above, if all you want are all locations that belong to a specific location id, you wouldn't need table 2. You could query just table 1:
select Location from Table1 where LocationID = <value>
where you would replace <value> with the LocationID you were looking for.
If you are looking for something else and I'm not reading the question correctly, could you post a sample set of data and an example of the specific results, based on the sample data, you want to have a query return?
-Ki
-Ki
March 1, 2011 at 8:01 am
ok, thank you for helping me figuring this out.
The database is very very simple I am going to give you an example set:
Table1 (master)
ID-----------------1
Location-----------Alaska
LocationID---------alaska001
Lat----------------65.730626
Long----------------154.160156
Map-------------this will be displaying lat/long asa google map(I have no problems with these setting)
Table2(child)-MapLocation
ID----------autonumber--in my case 1
LocationID--------alaska001--equal to the locationID from the Table 1
Map----- same automated (working processe as with table one)
SQL query
SELECT
Table1.ID,
Table1.LocationID,
Table1.Lat,
Table1.Long,
Table2.[Map]
FROM Table1
LEFT OUTER JOIN Table2 ON Table1.LocationID = Table2.LocationID
I would like to pull information from the Table1 corresponding to the LocationId.
Match 1 record of the table 1 with locationId to the record from the Table2 which has locationId equal to the table1.
I am sorry if this is not really clear, let me know how else to explain?
March 1, 2011 at 8:18 am
xeops,
Can you give me a couple more records for each table, and then an example of the specific data you would want to see in the results? That would make it a lot easier to help you figure this out.
Best,
- Ki
-Ki
March 1, 2011 at 8:24 am
Hi, Kiara,
I have uploaded the attachment in the mdb format.Thank you so much.
If you are able to see it online it looks like this right now
http://demo.asprunner.net/a_pehlevi_hotmail_com/mapping/menu.asp
click on the table 1
March 1, 2011 at 8:40 am
xeops (3/1/2011)
Hi, Kiara,I have uploaded the attachment in the mdb format.Thank you so much.
If you are able to see it online it looks like this right now
http://demo.asprunner.net/a_pehlevi_hotmail_com/mapping/menu.asp
click on the table 1
The files you sent me have only one locationid per location in them - four lines each. So I'm still a little confused about what you are trying to do.
Let me try to ask differently.
If you had the following data (you can copy and paste this into SQL and run it there if that helps):
create table Table1
(location varchar(20)
,locationID varchar(20)
)
create table Table2
(locationID varchar(20))
insert into Table1
(location, locationID)
values
('001','place001A')
insert into Table1
(location, locationID)
values
('002','place002A')
insert into Table1
(location, locationID)
values
('003','place003A')
insert into Table1
(location, locationID)
values
('001','place001A')
insert into Table1
(location, locationID)
values
('001','place002B')
insert into Table2
(locationID)
select distinct locationID from Table1
What would you want the output from your query to look like?
I based my sample data on the data in your table, but added some duplicates to make it easier to try to understand what you are looking for.
If you can tell us what the results you want should be, given the above data, it will be a lot easier to help you figure this out.
-Ki
-Ki
March 1, 2011 at 8:43 am
give me sec please, i am making the screenshots for you to see.
March 1, 2011 at 8:55 am
here they are
March 1, 2011 at 9:00 am
of course it would be as another solution to just enter manually data into Maps long/lat bu 10000 records are too much to enter 🙂
March 1, 2011 at 9:32 am
Ok. I *think* I get it. My apologies if I'm still not understanding - I was trying to understand the SQL results you were looking for, not what the application itself was trying to do.
If all you want to do is link the two tables based on locationId, try this:
select Table1.location, Table1.LocationID, Table1.*, Table2.*
from Table1
inner join Table2
on Table1.locationID = Table2.locationID
The above will ONLY produce results if locationID exists in BOTH tables. If you want data from Table1, even if there is nothing in Table2 to link to it, then your original left join will work fine.
I only included the Table#.* items to let you see all the data the join produces - I wouldn't code it that way in production.
I hope this is what you're looking for. If not, there's not much else I can do without seeing a simple list of the sample results you would expect from the sample data I provided you with.
Good luck!
-Ki
-Ki
March 1, 2011 at 9:34 am
Kiara (3/1/2011)
Ok. I *think* I get it. My apologies if I'm still not understanding - I was trying to understand the SQL results you were looking for, not what the application itself was trying to do.If all you want to do is link the two tables based on locationId, try this:
select Table1.location, Table1.LocationID, Table1.*, Table2.*
from Table1
inner join Table2
on Table1.locationID = Table2.locationID
The above will ONLY produce results if locationID exists in BOTH tables. If you want data from Table1, even if there is nothing in Table2 to link to it, then your original left join will work fine.
I only included the Table#.* items to let you see all the data the join produces - I wouldn't code it that way in production.
I hope this is what you're looking for. If not, there's not much else I can do without seeing a simple list of the sample results you would expect from the sample data I provided you with.
Good luck!
-Ki
One other thought just occurred to me - did you actually want to pull the data the other direction?
select Table2.locationID, Table1.*, Table2.*
from Table2
left join Table1
on Table2.locationID = Table1.locationID
The way your original data is set up, it almost looks like Table2 is actually the master location list and table 1 is the additional information you are looking for.
Good luck,
-Ki
-Ki
March 1, 2011 at 9:45 am
Hi, Kiara,
i am almost done with the project of mine. in an hour or so i would send a message for you to see the actual data.
I will try both approaches basically the project is to be described as follows:
there are 10000 waterfalls on thee planet earth. Almost 70 percent of them are having GPS coordinates.
I created 2 tables 1 master called Table1(or Waterfalls) and the detail table (table2 or Maps)
both are linked via field called Fall_ID. Fall_ID is never empty.It is manually inputed when a record in the table1 is created.
When detail link is clicked the corresponding only one GPS location is pulled.table 2 - or Maps is ver manualy populated but pulls its information from the Table1(Waterfalls) 2 fields Lat and Long.
you are really good help Kiara. See if this explanation helps you understanding the project?
March 1, 2011 at 9:49 am
xeops (3/1/2011)
Hi, Kiara,i am almost done with the project of mine. in an hour or so i would send a message for you to see the actual data.
I will try both approaches basically the project is to be described as follows:
there are 10000 waterfalls on thee planet earth. Almost 70 percent of them are having GPS coordinates.
I created 2 tables 1 master called Table1(or Waterfalls) and the detail table (table2 or Maps)
both are linked via field called Fall_ID. Fall_ID is never empty.It is manually inputed when a record in the table1 is created.
When detail link is clicked the corresponding only one GPS location is pulled.table 2 - or Maps is ver manualy populated but pulls its information from the Table1(Waterfalls) 2 fields Lat and Long.
you are really good help Kiara. See if this explanation helps you understanding the project?
So, if I understand correctly, there is a 1:1 relationship between the records in table1 and table2, and that relationship is defined by the field Fall_ID?
Table 2 is basically an extension to Table1?
If that's the case, why not link on Fall_ID, instead of worrying about the location ID?
-Ki
-Ki
March 1, 2011 at 9:51 am
yes, Kiara, you understand correctly, but i do not know how to do that.
I ma a total new be to the SQL 🙂
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply