PHP MySQL complex query

[Aludus]

Hello there

 

first of all: sry for my english, its maybe real bad

 

so, what i want to ask u... i do not realy know...

 

but let me explain my problem:

 

i've got 2 mysql tables in the same database there. "user" and "nickname"

 

the important things of "user" are "login" (the loginname), "email" and "id" (password isnt needed in this problem, as same as the other fields)

 

so, important in "nickname" is "nickname_plain" and "user_id" (user_id is the same as "id" to connect them to each other - in this table is also a field "id" for the nickname id, but not important for my problem, i think)

 

so here we go...

 

users can register more then once to my page, for multilogin and chatting. all a new registration, will add a new row into both tables. "user" contains important informations about the user self, "nickname" contains all other informations about the nick

 

so now, im working on a little tool for operators, to order them. simply input a nickname they see, a query will get the user_id out of "nickname". user_id will be used to get the email out of the "user" table, and the email to get all registered loginnames for this email/nickname.

 

but there is a problem. sometimes, the nickname_plain is diffrent from the loginname. my moderators will only see the loginname, not the shown nicknames.

 

so i've started trying to build up an query for the nicknames. here i need help from you.

 

how can i use the results of the email query (all loginnames) to get all the nicknames out of the other table?

every email field, gots its own "id" in the row, so... how can i sort them and start new requests for every id?

 

like.. 3 "id"s got all the same email - no problem to request and echo the loginnames

now how can i seperate the "id" results and start a new query to the nickname table for each seperated id?

[phpknight]

Put a short example with test field names showing what you want the desired result to be on a small dataset.  It is difficult to help without having an example.

[kickstart]

Hi

 

Not exactly sure on the issue you are having. Think the following should find the userids for a nicknames email address:-

 

SELECT c.login
FROM nickname a
JOIN user b
ON a.userid = b.id
JOIN user c
ON b.email = c.email
WHERE a.nickname = 'whatever'

 

All the best

 

Keith

[gevensen]

theres a freeware called toad that makes complex queries for you

i find it very helpful

[jazzman247]

I think the issue may be in how you are aliasing your table names? the table user has two names b and c in this example?

 

SELECT c.login

FROM nickname a

JOIN user b

ON a.userid = b.id

JOIN user c

ON b.email = c.email

WHERE a.nickname = 'whatever'