How can I join two tables while filtering the results with a" WHERE IN" clause? What I am trying to do is set up a selection filter for a cascading list on my site.  When I say selection filter, I mean I am trying to limit, and sort the results based on what the user selects.

I have set up the ability for my users to click a button and mark an item as a favorite.  That information is stored in "billdata.favoritebills".

Also, for efficiency I have saved vote totals for each bill in billdata.billvotesmeta.  billdata.billvotesmeta contains the column names: billid,totalvotes,yesvotes,presentvotes, and novotes. The primary key is billid.

My database is set up as follows:

billdata.bills

billdata.favoritebills

billdata.billvotesmeta

In this particular filter I am trying to set up, I want to limit the results to only being ones that the user has marked as a favorite and at the same time I want to order them from least to greatest total number of votes. 

billdata.favoritebills contains the column names: userid and billid and a combination either exists in marked as a favorite or is deleted if it is unmarked as a favorite.

$testquery = "SELECT * FROM bills INNER JOIN bills ON billdata.billvotesmeta.billid = billdata.bills.id WHERE id IN ( SELECT billid FROM billdata.favoritebills WHERE userid='$_SESSION[userid]' ) ORDER BY DESC billdata.favoritebills.totalvotes";

$result = mysqli_query(dbcxn('bill'),$testquery)
     or mysqli_debug();

$fetched = mysqli_fetch_assoc($result);


The resulting output from PHP is:

Warning: mysqli_debug() expects exactly 1 parameter, 0 given in /opt/lampp/htdocs/unipartisan/includes/billlistcompiler.inc on line 38

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in /opt/lampp/htdocs/unipartisan/includes/billlistcompiler.inc on line 39
1NULL


What should happen is that it limits the billdata.bills.* selection to only being favorites and then orders them by the joined table's total vote count.  The data from billdata.bills is the information used to build the page.

I think the problem is in the " WHERE id IN ( SELECT billid FROM billdata.favoritebills WHERE userid='$_SESSION[userid]' ) maybe this is not compatible with a query that has a join as I have not found anywhere else detailing a WHERE IN clause along with a join.
"
not familiar with mySQL syntax, but I believe there are multiple errors with your query. you've got an extra ")" or you tried to use "order by" in a subquery, the join clause looks weird, it's returning everything and there is no group by with sum(votes), etc.

For problems like these, it is best to start with a working query and expand it one step at a time. For example, first join 2 tables, then change the * to the list of fields you want, then restrict it with the third, then sum up votes, then sort them, ...

try:

SELECT b.id,sum(bm.totalvotes)
FROM 
bills b 
inner join favoritebills fb on b.id = fb.billid
inner join billvotesmeta bm on b.id = bm.billid
WHERE 
userid='$_SESSION[userid]'
group by b.id
order by sum(bm.totalvotes) desc;


You may have to rename some fields. the names aren't clear to me. 
if you want to add fields to your resultset, be sure to add them to the group by clause at the bottom too.