2 queries, 1 result

3rd day trying... please help tell me what on earth i'm doing wrong, or better yet, the right way.
I am trying to get 1 result from 2 different queries, as follows:




The code must be wrong, i know... but i've looked & looked & can't figure out the right way. and if anyone knows the right way to do this, please respond thank you!

( ps. i saw this thread... URL but it prints out results for each query... i only want 1 result to print. For the 1st query to result in something that the 2nd query uses to get a single result )

Looks like you'll just need to run the two queries separately and combine the results after the fact. You're getting a syntax error in your mysql_query line because you're closing your double quotes but continuing to type out a string. Even if you weren't getting a PHP error, the SQL is malformed. In a simple statement like this, you can have only one FROM keyword and one WHERE keyword. If you're bent on knocking this out with one query, perhaps you'd provide your table definition and describe precisely what you're trying to make happen (the field names make it hard to guess).

(first off, i am obviously a newbie, please please please excuse the lack of proper terminology, thank you ).

Ok, in case I'm awful at explaining this, here is a pic of the table, which has changed since I was up all night trying to figure out a query/ queries that would work.

URL

AffID = Member
Sp1= Passcode
RB = The Member that Referred another AffID (Member)

What I'm trying to do is allow an AffID (member) to view the Sp1 (passcode) of the member that referred him/her (RB).

So i am trying to get the query/queries to find the Sp1(passcode) in the row of the referring member(RB) for the AffID that is currently logged into the site (caffid).

That's the other 'key' i used in the posted erroneous query, which is not in any table, it's just a cookie session thing for when an member is signed in. I have i set that for there session (basically) they are a caffid = cookied affid(member). *Just wanted to add that explanation since there is "caffid" in the code posted below (or above) depending on which way this forum thread is viewed.

What I *thought* would make sense has basically turned into a nightmare since no matter what i do i still have 2 queries there, and have absolutely no idea how to "just run the two queries separately and combine the results after the fact."

I have tried & tried & tried again to do a left join but honestly, it hasn't worked yet. It seems like it's just a circular reference. Any help is so greatly appreciated. Thanks

Ah, I see the problem. You're wanting to get Sp1 where AffID=RB for the current user, but in order to do that, you also have to determine who the current user is so that you can get the RB, which means that you're selecting where AffID=RB and AffID=caffid, which as you note doesn't really work.

I'd propose dropping RB from your current table and breaking the referrals out into another table, something like:

id int(10) unsigned zerofill not null primary key,
AffID int...
RB int...

The id field just gives you a unique key, though you could also just make correlations between AffID and RB a unique key.

Then your query could run as follows:



If there (erroneously or not) are multiple RB values in the referrals table for a given AffID, this'll return them all.

Or, it just occurred to me, if you don't want to change your table structure, you can join the table to itself, as follows:



I replicated your database on my system and this query worked like a charm. Note that where I've hard-coded 74 in the query, you'd use your $caffid.

Impressed beyond belief here. Most excellent !!!!!!!!!!!

Thank you so much for your help