Jump to content


Photo

Sorting by another Table


  • Please log in to reply
18 replies to this topic

#1 InterDevelop

InterDevelop
  • Members
  • PipPip
  • Member
  • 13 posts
  • LocationGreenville, SC

Posted 19 May 2006 - 05:29 PM

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:

    $sql = "SELECT * FROM Members WHERE MemberID = 552 || MemberID = 562 ORDER BY ServiceProvided.Ranking";
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\" /]

#2 char0n

char0n
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 19 May 2006 - 05:42 PM

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

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 20 May 2006 - 03:36 PM

[!--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) View Post[/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:

    $sql = "SELECT * FROM Members WHERE MemberID = 552 || MemberID = 562 ORDER BY ServiceProvided.Ranking";
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?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 InterDevelop

InterDevelop
  • Members
  • PipPip
  • Member
  • 13 posts
  • LocationGreenville, SC

Posted 24 May 2006 - 01:07 PM

[!--quoteo(post=375516:date=May 20 2006, 11:36 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ May 20 2006, 11:36 AM) View Post[/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 Services table to match a search query of up to 72 items selected. This give me the list of MemberID to pull from the Members table. However, in the Services table, I have a seperate field to do a rank (ORDER BY). The ranking is not in the Members table, so I need to get the member details from the Members table but ORDER BY the "ranking" field in the Service table. Hopefully this clarifies it.

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 24 May 2006 - 03:40 PM

Try the following (UNTESTED):

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
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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 InterDevelop

InterDevelop
  • Members
  • PipPip
  • Member
  • 13 posts
  • LocationGreenville, SC

Posted 10 July 2006 - 08:25 PM

I think I'm almost there..... sort of...
My final sql statement:
$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";
  • $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.
I do get results, but the ordering still doesn't quite sort it by Rank. is there another way to do this...?

::)

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 11 July 2006 - 08:24 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#8 InterDevelop

InterDevelop
  • Members
  • PipPip
  • Member
  • 13 posts
  • LocationGreenville, SC

Posted 11 July 2006 - 07:47 PM

hmmm.  I chose to use DISTINCT because if I don't, the results repeat 44 times from the above the following query:
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
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?

#9 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 12 July 2006 - 03:11 AM

Hard to say without the data in hand; you're probably missing a GROUP BY clause somewhere.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#10 InterDevelop

InterDevelop
  • Members
  • PipPip
  • Member
  • 13 posts
  • LocationGreenville, SC

Posted 12 July 2006 - 02:58 PM

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?

#11 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 12 July 2006 - 03:39 PM

Really? Can you output the result of your query, including the resRanked column? It doesn't make any sense.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#12 InterDevelop

InterDevelop
  • Members
  • PipPip
  • Member
  • 13 posts
  • LocationGreenville, SC

Posted 12 July 2006 - 04:22 PM

This is the full query that is passed based on selection criteria:
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
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...??

#13 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 12 July 2006 - 05:09 PM

I'm confused... what does the first set of results represent, versus the 2nd set? 
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#14 InterDevelop

InterDevelop
  • Members
  • PipPip
  • Member
  • 13 posts
  • LocationGreenville, SC

Posted 12 July 2006 - 06:14 PM

The first set of results ---
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) 
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)...
  • 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.
The IDs are correct, but the ranking of these IDs is where I get stuck. Hopefully this clarifies a little.  ???

#15 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 12 July 2006 - 06:41 PM

I've obviously missed something.. your resRanked varies from 1-4?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#16 InterDevelop

InterDevelop
  • Members
  • PipPip
  • Member
  • 13 posts
  • LocationGreenville, SC

Posted 12 July 2006 - 06:45 PM

:-\ 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.

#17 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 12 July 2006 - 08:06 PM

Still, I'm not sure why your ORDER BY wouldn't catch... resRanking, memberID should do the trick.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#18 InterDevelop

InterDevelop
  • Members
  • PipPip
  • Member
  • 13 posts
  • LocationGreenville, SC

Posted 12 July 2006 - 08:10 PM

??? I'm not sure either.... The sorting works for the
ORDER BY MemberTypeID ASC

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

#19 SQL_F1

SQL_F1
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 13 July 2006 - 05:53 AM

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. 
Which service ranking do you want?

problem #1
Provide the actual result set from your query - I can't tell in what order it is 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
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;

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users