Possible? Select two fields with distinct? 2

[Tasuki]

I'm having a lot of trouble with this one.

Here is my query:

SELECT DISTINCT(LOCATION) AS LOC FROM tblNetDesc 
     LEFT JOIN tblNetClass ON tblNetClass.NetClassID=tblNetDesc.NetClassID
     LEFT JOIN tblLocation ON tblLocation.LocationID=tblNetClass.LocationID;

This works great and returns the following:

Lisanki Island
Pearl & Hermes Atoll
Kure Atoll
Midway Atoll

Problem is I need to retrieve the LocationID along with it. So I would get something like this:

1 Lisanki Island
2 Pearl & Hermes Atoll
3 Kure Atoll
4 Midway Atoll

Is it possible to retrieve the LocationID that relates to each of the values returned?

Any help is appreciated.

Thanks,

-T

 

[azzazzello]

Assuming all unique LOCATIONs have identical LocationID, just add that to what you are pulling out!

SELECT LocationID, DISTINCT(LOCATION) AS LOC ...

In case that identical LOCATIONs do not have the same LocationID, you will need to use GROUP BY, as in

SELECT LocationID,LOCATION AS LOC ... GROUP by LOCATION, LocationID

 

[Tasuki]

Thanks for that, I'm currently trying with the GROUP BY. Keep getting "LocationID in group statement is ambiguous" or something in MySQL. Will keep testing.

 

[Tasuki]

The GROUP BY works great! The ambiguous problem occurred because two tables contained the same field, so I had to use something like tblNetClass.LocationID.

This is what I finally have if anyone may find it useful:

SELECT tblNetClass.LocationID AS LOCID, Location AS LOC FROM tblNetDesc
     LEFT JOIN tblNetClass ON tblNetClass.NetClassID=tblNetDesc.NetClassID
     LEFT JOIN tblLocation ON tblLocation.LocationID=tblNetClass.LocationID
     GROUP BY LOCID, LOC;

Which gave me exactly what I was looking for:

1 Kure Atoll
2 Midway Atoll
3 Pearl & Hermes Atoll
4 Lisanki Island

I couldn't get it to work using the Distinct, but the GROUP BY did the job.

Thanks for pointing me in the right direction, Azzazzello.

-T

 

[r937]

please show query

if there are dupes, and i expect there are, otherwise you wouldn't be using DISTINCT, then showing the ID of only one of the dupes requires some rule to select which ID you want to show -- the lowest? the highest?

what table is the LOCATION column in?



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Tasuki]

Note: The LocationID matches with the Location in tblLocation, but I could not get the DISTINCT to work and display two returned fields. So, I'm still unsure if it's possible to do it that way or not.

Thanks again.

 

[r937]

this will also work --

select [b]distinct[/b]
       tblNetClass.LocationID as LOCID
     , Location    as LOC 
  from tblNetDesc
left 
  join tblNetClass 
    on tblNetDesc.NetClassID
     = tblNetClass.NetClassID
left 
  join tblLocation 
    on tblNetClass.LocationID
     = tblLocation.LocationID

DISTINCT is not a function ;-)


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Tasuki]

Hey r937,

Yes there is duplicates, I only want to show Distinct so I can add those fields to a SELECT box in PHP...

In case anyone is wondering what I'm trying to do... I'll try to explain:

I'm using this SQL statement in creating a sort of dynamic search form in PHP, there will be a WHERE in the SQL to make changes to the overall query.

The form contains a bunch of categories: LOCATION, COLOR, NETTYPE... etc.

So when they select for example color RED, the query will update adding the WHERE COLOR=RED and return all nets that are RED.. and in my problem above (which will be addressed for all categories I plan to have) it will select LOCATION where NET=RED. This query will update the SELECT box with DISTINCT locations where NET=RED. So now they could query on the current location results where NET=RED.

A table will list all records returned at the bottom of the form based on the new query. Hope that wasn't too confusing.

Thanks for your questions and help.

-T

 

[Tasuki]

Yes, that works just fine in MySQL also. Although it's not ordered as in using GROUP BY. Anyway, that dispels my thoughts that it's not possible to do.

Thanks for that, Distinct is good to use also.