Sorting by another Table

[InterDevelop]

I have query that gives me the perfect set of results I'm looking for. It's narrowed things down from a location table and a services provided table. I use the results (i.e. Member IDs) to pull contact info out of the membership table. However, I need to sort by a ranking number of 1 or more services provided. I'm stumped on how to do an ORDER BY from a field in another table. Has anyone worked with something like this?

I've tried the following, but it doesn't work:

[code]    $sql = "SELECT * FROM Members WHERE MemberID = 552 || MemberID = 562 ORDER BY ServiceProvided.Ranking";[/code]
My code is amaturish at best, but trying to get some input that would help me become a better MySQL/PHP developer. :blink:

Thanks,

Jeff [img src=\"style_emoticons/[#EMO_DIR#]/huh.gif\" style=\"vertical-align:middle\" emoid=\":huh:\" border=\"0\" alt=\"huh.gif\" /]

[char0n]

well, i am not sure if i understood it correctly but here is my solution.
I just guess u have 2 related tables and u want to sort values from
one table according to the value from another

SELECT t1.ID, t1.text FROM table1 AS t1
LEFT JOIN table2 AS t2 ON t1.ID=t2.ID
WHERE t1.ID='1'
ORDER BY t2.Rank ASC LIMIT 0, 30

Hope it little bit helped u

[fenway]

[!--quoteo(post=375286:date=May 19 2006, 01:29 PM:name=Jeff Baker)--][div class=\'quotetop\']QUOTE(Jeff Baker @ May 19 2006, 01:29 PM) [snapback]375286[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I have query that gives me the perfect set of results I'm looking for. It's narrowed things down from a location table and a services provided table. I use the results (i.e. Member IDs) to pull contact info out of the membership table. However, I need to sort by a ranking number of 1 or more services provided. I'm stumped on how to do an ORDER BY from a field in another table. Has anyone worked with something like this?

I've tried the following, but it doesn't work:

[code]    $sql = "SELECT * FROM Members WHERE MemberID = 552 || MemberID = 562 ORDER BY ServiceProvided.Ranking";[/code]
My code is amaturish at best, but trying to get some input that would help me become a better MySQL/PHP developer. :blink:

Thanks,

Jeff [img src=\"style_emoticons/[#EMO_DIR#]/huh.gif\" style=\"vertical-align:middle\" emoid=\":huh:\" border=\"0\" alt=\"huh.gif\" /]
[/quote]
How are these tables linked?

[InterDevelop]

[!--quoteo(post=375516:date=May 20 2006, 11:36 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ May 20 2006, 11:36 AM) [snapback]375516[/snapback][/div][div class=\'quotemain\'][!--quotec--]
How are these tables linked?
[/quote]
Linked by MemberID in both tables. I think my problem is that I have several ServiceIDs & several MemberIDs that I'm trying to process.

I call the [b]Services[/b] table to match a search query of up to 72 items selected. This give me the list of MemberID to pull from the [b]Members[/b] table. However, in the [b]Services[/b] table, I have a seperate field to do a rank (ORDER BY). The ranking is not in the [b]Members[/b] table, so I need to get the member details from the [b]Members[/b] table but ORDER BY the "ranking" field in the [b]Service[/b] table. Hopefully this clarifies it.

[fenway]

Try the following (UNTESTED):

[code]SELECT m.*
FROM Members AS m
LEFT JOIN Services AS s ON ( s.MemberID = m.MemberID )
WHERE m.MemberID IN ( '552', '562' )
ORDER BY s.Ranking[/code]
Technically, you're not supposed to be allowed to use a column in a order by that doesn't appear in the column list, but MySQL lets it slide.

[InterDevelop]

I think I'm almost there..... sort of...
My final sql statement:
[code]
$sql = "SELECT DISTINCT mem.MemberID, mem.FirstName, mem.MiddleName, mem.LastName, mem.CompanyName, mem.MemberTypeID,
mem.MemberLevel, mem.Addr1, mem.Addr2, mem.City, mem.State, mem.Zip, mem.Work, mem.Fax, mem.Email FROM ASIDMembers AS mem
LEFT JOIN ResServicesProvided AS res
ON (res.MemberID=mem.MemberID) WHERE mem.MemberID IN ($members_by_location3) ORDER BY res.ResRanked";
[/code]
[list]
[*]$members_by_location3 is an array of members passed from a location table earlier on in the search... (1, 201, 144, 509). This limits the results to the correct people, but the ORDER BY element does not order proplery.
[*]The res.ResRanked field is a numeric value that theoretically should sort by numbers 1-50.
[/list]
I do get results, but the ordering still doesn't quite sort it by Rank. is there another way to do this...?

::)

[fenway]

I'm not sure how that's possible... you should return res.ResRanked in your column list, and see what values you get back.  Also, be careful with DISTINCT, especially with so many fields -- it can give you strange results.

[InterDevelop]

hmmm.  I chose to use DISTINCT because if I don't, the results repeat 44 times from the above the following query:
[code]
SELECT DISTINCT
mem.MemberID, mem.FirstName, mem.MiddleName, mem.LastName, mem.CompanyName,
mem.MemberTypeID, mem.MemberLevel, mem.Addr1, mem.Addr2, mem.City, mem.State,
mem.Zip, mem.Work, mem.Fax, mem.Email, res.MemberID, res.ResRanked
FROM
  ASIDMembers AS mem
  LEFT JOIN ResServicesProvided AS res
ON (res.MemberID=mem.MemberID)
WHERE
  mem.MemberID IN ($members_by_location3)
ORDER BY
  res.ResRanked, mem.MemberTypeID
[/code]
I've included the res.MemberID and res.ResRanked in my column list. It seams to pull a better set of results. There still some weird things happening.

:o The first issue is that this returns the same results 44 times, even though the list of MemberIDs in the WHERE memMemberID IN ($members_by_location3) statement only is in there once. Any thoughts?

[fenway]

Hard to say without the data in hand; you're probably missing a GROUP BY clause somewhere.

[InterDevelop]

o.k.... by using a GROUP BY on the mem.MemberID field, I only get one member returning with the result set... the problem now is that the ORDER BY res.ResRanked field isn't ordering at all. The res.ResRanked field is a TINYINT. Is there an issue sorting by this type of field?... should I change the field type to a text (VARCHAR or TEXT) field type? Should I use letters instead of numbers for these fields if I want to sort by them?

[fenway]

Really? Can you output the result of your query, including the resRanked column? It doesn't make any sense.

[InterDevelop]

This is the full query that is passed based on selection criteria:
[code]
SELECT
  mem.MemberID, mem.FirstName, mem.MiddleName, mem.LastName, mem.CompanyName,
  mem.MemberTypeID, mem.MemberLevel, mem.Addr1, mem.Addr2, mem.City, mem.State,
  mem.Zip, mem.Work, mem.Fax, mem.Email, res.MemberID, res.ResRanked
FROM
  ASIDMembers AS mem
  LEFT JOIN ResServicesProvided AS res
ON (res.MemberID=mem.MemberID)
WHERE
  mem.MemberID IN (48, 64, 66, 84, 100, 169, 173, 181, 277, 287, 545, 546, 562,
  579, 592, 599, 600, 616, 642, 663, 667, 675, 698, 743)
GROUP BY
  mem.MemberID
ORDER BY
  res.ResRanked ASC, mem.MemberTypeID ASC
[/code]
The res.ResRanked column (res.MemberID = res.ResRanked) results are:
48=1
64=1
84=1
100=1
181=1
545=1
579=1
592=1
600=1
663=1
667=1
675=1
698=1
66=2
169=2
173=2
277=2
287=2
616=2
642=2
743=2
546=3
562=4
599=4

So all the res.MemberIDs that = 1 (i.e. res.ResRanked =  1) should show up first and then 2, then 3, then 4... Hoever, the ordering of the results is as followis:
675, 698, 546, 579, 277, 545, 592, 667, 599, 562, 181, 287,
100, 600, 64, 84, 48, 642, 616, 663, 169, 66, 173, 743
So... I'm not sure why it is not ordering by res.ResRanked...??

[fenway]

I'm confused... what does the first set of results represent, versus the 2nd set? 

[InterDevelop]

The first set of results ---
[code]
WHERE
  mem.MemberID IN (48, 64, 66, 84, 100, 169, 173, 181, 277, 287, 545, 546, 562,
  579, 592, 599, 600, 616, 642, 663, 667, 675, 698, 743)
[/code]
are a list of MemberIds that match a set of criteria from from a locations (i.e. city code match) table and a services provided table. I use these IDs to retreive the members data out of the membership table (ASIDMembers). Part of the process is displaying the order by how the member ranked a certain service in the services table (ResServicesProvided).

To simplify (hopefully)...
[list]
[*]I have a set of results (1 or more member IDs).
[*]I retreive the details from the members table.
[*]I need to sort these results by a ranked number (possibly values of 1-30 - res.ResRanked) from the services provided table.
[/list]
The IDs are correct, but the ranking of these IDs is where I get stuck. Hopefully this clarifies a little.  ???

[fenway]

I've obviously missed something.. your resRanked varies from 1-4?

[InterDevelop]

:-\ Those particular ranking were specific to that set of IDs. There are other member IDs in the system that may have a ranking as high as 30.

[fenway]

Still, I'm not sure why your ORDER BY wouldn't catch... resRanking, memberID should do the trick.

[InterDevelop]

??? I'm not sure either.... The sorting works for the
[code]
ORDER BY MemberTypeID ASC
[/code]
segment, but the other part just isn't catching... I'm going to take a further look at the actual data in the tables and see if I'm still missing something. Thank you for taking some time with me on this. If I find out anything new, I'll post back to here.

Thank you - Jeff

[SQL_F1]

Hi Jeff
You're in luck - I'm using this example in a SQL class I'm preparing so I have some time to devote to the problem

There are 2 problems:
1) We have been using  a JOIN when we should not have JOINed or JOINed in another way.  That resulted in duplicates which we then suppressed in a random way because of problem #2

2) There is a hole in the business logic of the query.

#2 first because #1 cannot be solved without knowing what we actually need.

You have a Members table, probably with unique MemberID's/1 row per member in a 1 to 0, 1 or many relationwhip with a Services table joined on MemberID.

I presume that most Members have ranked 1 or more Services and some have not ranked any (because you used a left join)

So you have Members with multiple rankings, one for each Service. 
[b]Which service ranking do you want?[/b]

problem #1
Provide the actual result set from your query - I can't tell in what order it [u][b]is[/b][/u] appearing in.

There's no way it will not appear in res.ResRanked ASC, mem.MemberTypeID ASC order in the full query you set out (with the ORDER BY res.ResRanked ASC, mem.MemberTypeID ASC ) - tinyints for values 1 - 30 are just fine - varchar will be even worse (1, 10, 11 ... 2, 21, 22..)

The problem with the JOIN and GROUP BY/DISTINCT is that there is no specifying which ranking you want
GROUP BY should only be used with aggregate functions (MIN, MAX  ) because they specifiy which values you want for each value grouped - if they can't the GROUP BY is inappropriate.

The other problem with the query is that it may be the wrong way around.
Members are being identified by location and Services provided, then those members are listed with a ranking from the services provided table.

The following seems to be what should is required
[code]SELECT
res.Service, res.ResRanked, 
mem.MemberID, mem.FirstName, mem.MiddleName, mem.LastName,
mem.CompanyName, mem.MemberTypeID,
mem.MemberLevel, mem.Addr1, mem.Addr2, mem.City, mem.State, mem.Zip,
mem.Work, mem.Fax, mem.Email
  FROM
ResServicesProvided AS res
INNER JOIN ASIDMembers AS mem 
  ON (res.MemberID=mem.MemberID)
WHERE mem.MemberID IN ($members_by_location3)
ORDER BY res.ResRanked;
[/code]

If this is not what is intended (see problem #2) then there must be some criteria missing from the WHERE clause specifying which single service you want to the the ranking for.

I look forward to your resonse.
Brian