complex SQL SELECT statement

There are 3 tables, VendorLists, Vendors, and Referrals.

VendorLists is a linking table. It has VendorListID, VendorID, and ListID fields.

Vendors is linked to VendorLists through the VendorID field (one to many)

Referrals is linked to VendorLists through VendorListID (one to many)

I'm given a value for ListID and have to pull records from both the Vendors and Referrals table (a referral is a description of a vendor, one to many).

I am able to do this with the following SQL select statement:

SELECT Referrals.Description, Vendors.Company
FROM Referrals CROSS JOIN Vendors
WHERE Referrals.VendorListID IN
(SELECT VendorListID FROM VendorLists WHERE (ListID = lid))
AND
(Vendors.VendorID IN (SELECT VendorID FROM VendorLists WHERE ListID = lid))
ORDER BY Vendors.VendorID

This pulls all the appropriate records and values that i need and orders them by the identifier for the vendor. However, I want to randomly order the vendors but still group them together by company, so, if the VendorID is 1 for "joe's crab shack" and 2 for "billy's ice cream shop", the above will always list joe's crab shack first and all it's referrals. i want to be able to randomly order the vendors, but still keep the referrals of those vendors grouped together so that when i iterate over them, they're grouped.

Does anyone have any idea how to do this? I'm stumped!!

i'm not gonna even attempt to answer your question about ordering randomly until i understand your query

the normal way to join the three tables involved in that many-to-many structure, which is, by the way, extremely common, does not involve subqueries

i am finding it particulary difficult to understand which of your tables have which columns, so i'll just show you a different structure, and trust that you can translate to your own situation

take a close look at that query

it really is as simple as it gets

Well, you have a query that gives you the results that you want (I'm gonna put aside diff writing suggestions), but you're ordering by VendorID, and you don't want to, you want to order by Company...why is it not feasable to just change your order by??

You're right, that is as simple as it gets. Unfortunately, perhaps of my own fault, it doesn't answer my question.

In your example, you're matching them on the same ID. I have one ID that i use to get two other ID's, and *THEN* on those ID's I create the JOIN--this is why i used subqueries. do you know a better method?

Additionally, the problem of sorting is unresolved. Yes you're able to pull the records, and i'm able to do so with the query that i listed. However, there are multiple referrals for each vendor and i need the referrals grouped together with each of the vendors, but i need those vendors randomly sorted (but STILL grouped with the referrals). this is the problem i have. i can sort them by company name, vendorID etc, but NOT by NEWID() because then the referrals are no longer grouped to their appropriate vendor.

Is this anymore clear?

Company is basically a friendly version of a vendorID, it's unique but the name of the company. I could order by any of them, as you asked, but the problem is they would always be in the *same* order everytime. Essentially i want to ORDER BY NEWID(), however, when this is done, all of the referrals for each of the companies become un-grouped. I need them grouped together, but randomly ordered.

for example, let's say there are two companies "companyA" and "companyB" for companyA there are 4 referrals and for companyB there are 5 referrals. In the above SQL, it would list:

companyA - referral 1
companyA - referral 2
...
companyB - referral 1
...

always in that order. i want it to be able to list them:

QUERY 1:

companyB - referral 1
companyB - referral 2
...
companyA - referral 1
...

QUERY 2:
companyA - referral 1
companyA - referral 2
...
companyB - referral 1
...


So that the query results are random but still remain grouped together. No order by clause can do this, so it must be done, somehow, in the query, but subqueries don't allow a order by either.