stupid mysql query

[Monkuar]

all i am doing is trying to left join my column row  "to_id" and "from_id"

 

i can successfully left join to_id but once i try to add "from_id" to the "ON" clause it makes all my data blank

 

SELECT gold_logs.*,name,star 
	FROM gold_logs LEFT JOIN ibf_members ON to_id = ibf_members.id
	WHERE to_id = 1

 

 

That query works fine, but when i try to add my "from_id" column the query works with no error's but no results are shown

 

SELECT gold_logs.*,name,star 
	FROM gold_logs LEFT JOIN ibf_members ON (to_id = ibf_members.id AND from_id = ibf_members.id)
	WHERE to_id = 1

 

 

[AyKay47]

Then your conditions are not being met, how can ibf_members.id equal two values at once?

Perhaps you meant OR not AND

[blacknight]

once you left join you have to declare each table each field is from

im guessing form_id and to_id are in gold_logs

 

SELECT gold_logs.*,name,star 
FROM gold_logs 
LEFT JOIN ibf_members 
ON (gold_logs.to_id = ibf_members.id AND gold_logs.from_id = ibf_members.id)
WHERE gold_logs.to_id = 1

 

and see if that works..

[Monkuar]

once you left join you have to declare each table each field is from

im guessing form_id and to_id are in gold_logs

 

SELECT gold_logs.*,name,star 
FROM gold_logs 
LEFT JOIN ibf_members 
ON (gold_logs.to_id = ibf_members.id AND gold_logs.from_id = ibf_members.id)
WHERE gold_logs.to_id = 1

 

and see if that works..

 

This does the same thing as before, shows no information?

 

The problem is

 

to_id = 1

 

from_id = 5

 

So that means user with a id = 5 sent 200 Gold to to_id 1 (user id 1),

 

Now I just need to left join both those columns (from_id/to_id) on ibf_members, so I can extract the name of EACH user.

[AyKay47]

once you left join you have to declare each table each field is from

im guessing form_id and to_id are in gold_logs

 

SELECT gold_logs.*,name,star 
FROM gold_logs 
LEFT JOIN ibf_members 
ON (gold_logs.to_id = ibf_members.id AND gold_logs.from_id = ibf_members.id)
WHERE gold_logs.to_id = 1

While this is good practice, it is only for ambiguity purposes, and this case in particular it will not affect the outcome.

OP, simply join the table using the from_id. The where clause will control your main table.

 

SELECT gold_logs.*,name,star 
FROM gold_logs 
LEFT JOIN ibf_members 
ON gold_logs.from_id = ibf_members.id
WHERE gold_logs.to_id = 1

[Monkuar]

once you left join you have to declare each table each field is from

im guessing form_id and to_id are in gold_logs

 

SELECT gold_logs.*,name,star 
FROM gold_logs 
LEFT JOIN ibf_members 
ON (gold_logs.to_id = ibf_members.id AND gold_logs.from_id = ibf_members.id)
WHERE gold_logs.to_id = 1

While this is good practice, it is only for ambiguity purposes, and this case in particular it will not affect the outcome.

OP, simply join the table using the from_id. The where clause will control your main table.

 

SELECT gold_logs.*,name,star 
FROM gold_logs 
LEFT JOIN ibf_members 
ON gold_logs.from_id = ibf_members.id
WHERE gold_logs.to_id = 1

 

 

1 Problem

 

 

Using your query my results come out:

 

 

 

Problem is, the name should not be the "the doctor" because it's not id 1, any idea on the fix?

 

it should be the name of the whoever ahs user ID 1 in ibf_members

[blacknight]

opps yea i see that now too some one shouldent be the sender and the reciver at the same time which is what the on statement is stating

if you use ON (gold_logs.to_id = ibf_members.id OR gold_logs.from_id = ibf_members.id) it will list both sent and recived

 

[Monkuar]

opps yea i see that now too some one shouldent be the sender and the reciver at the same time which is what the on statement is stating

if you use ON (gold_logs.to_id = ibf_members.id OR gold_logs.from_id = ibf_members.id) it will list both sent and recived

 

wont work because of the where id = 1

 

and it would show 3 rows instead of my 2

 

i just need to left join my column to_id and from_id to the column ID from ibf_members

 

iuno this is so hard, i must need to read more mysql @_@

[AyKay47]

show both of your mysql tables, it may be that ibf_members needs to be the left table

[Monkuar]

show both of your mysql tables, it may be that ibf_members needs to be the left table

 

my ibf_members only has 3 columns

 

id

 

name

 

star

 

 

 

should i re do my query but use ibf_members as the main 1 and LEFT JOIN the gold_logs? try that?

 

i am really stumped it's almost working, it SHOULD work when i use the ON clause cause essentially i just want it to pull the name off ibf_members to correspond to each to_id and from_id....

 

[AyKay47]

SELECT gold_logs.*,members1.name,members1.star,members2.name,members2.star 
FROM gold_logs 
LEFT JOIN ibf_members members1
ON ibf_members.id = gold_logs.to_id 
LEFT JOIN ibf_members members2
ON ibf_members.id = gold_logs.from_id
WHERE gold_logs.to_id = 1

[Monkuar]

SELECT gold_logs.*,ibf_members.name,ibf_members.star 
FROM gold_logs 
LEFT JOIN ibf_members 
ON ibf_members.id = gold_logs.to_id 
LEFT JOIN ibf_members 
ON ibf_members.id = gold_logs.from_id
WHERE gold_logs.to_id = 1

 

I get

 

mySQL error: Not unique table/alias: 'ibf_members'

 

Seems fishy

 

why would it think it's not unique?

[AyKay47]

yeah, refer to my edit

[Monkuar]

yeah, refer to my edit

holy monkeys

 

mySQL error: Unknown column 'ibf_members.id' in 'on clause'

 

This is weird, man sorry for the trouble

[AyKay47]

not your fault, I continuously keep messing up the query, its been a long day.. refer to my edit again in a minute

[AyKay47]

never mind, i can't edit the post anymore, here

 

SELECT gold_logs.*,members1.name,members1.star,members2.name,members2.star 
FROM gold_logs 
LEFT JOIN ibf_members members1
ON members1.id = gold_logs.to_id 
LEFT JOIN ibf_members members2
ON members2.id = gold_logs.from_id
WHERE gold_logs.to_id = 1

 

 

[Monkuar]

never mind, i can't edit the post anymore, here

 

SELECT gold_logs.*,members1.name,members1.star,members2.name,members2.star 
FROM gold_logs 
LEFT JOIN ibf_members members1
ON members1.id = gold_logs.to_id 
LEFT JOIN ibf_members members2
ON members2.id = gold_logs.from_id
WHERE gold_logs.to_id = 1

 

Hey, it's working now but I don't want to walk away from this seeming like I am just asking for help just "cuz" I want to actually learn this stuff.  Which your query helped me greatly.

 

now the problem is, how do I echo that out so I can differentiate between my to_id and from_id's ?

 

 

This is how i echo it out in php

 

<a href=?i={$r['to_id']}>{$r['name']}</a>

That will show me the name and link them to there to_id, (which is there profile)

 

 

Then for from:

<a href=?i={$r['from_id']}>{$r['name']}</a>

 

it's the same thing, but it's showing up weird here:

 

 

 

See it's almost working now!

 

But that to_id should have the username "Newman" hence that is id #1 from ibf_members, but it shows the id from 25 instead which shows the user id of 25's name which is "theDoctor"

 

sorry this is really prob pissing you off, but this stuff has been bothering me the past 20hours and I  have no where else to go

 

 

[Monkuar]

WOW I FIXED IT DUDe

 

FINALLY

 

members1.name as to_name,members1.star,members2.name as from_name

 

WOW TOPIC RESOLVED DUDE THANK YOU SO MUCH :Dddddddddddddddddddddddd

 

I guess I just needed to add the "AS ETC"

 

THANKS SO MUCH BRO

[AyKay47]

WOW I FIXED IT DUDe

 

FINALLY

 

members1.name as to_name,members1.star,members2.name as from_name

 

WOW TOPIC RESOLVED DUDE THANK YOU SO MUCH :Dddddddddddddddddddddddd

 

I guess I just needed to add the "AS ETC"

 

THANKS SO MUCH BRO

no problem, im glad you figured that last part out on your own. good work