a question about sql join

[bikashliju]

hello sir,

i am a fresh php developer , now i face a problem in sql queries (i only know a little about sql)
my table model is like this

TABLE1 NAME -:EMPLOYEE

ename eid dept

RAM 001 HR
HARI 002 software


TABLE 2 NAME:- ADMIN

username password eid
HR secureyes 001

select * from employee as e left join admin as a on a.eid=e.eid

i got a resultant table where eid comes twice

ename eid dept username password eid
RAM 001 HR admin secureyes 001
hari 002 software null null 002
1.how to eliminate this second eid from resultant table.

2.is there anyway to name the this above resultant table
3.i want to retrive all details of "hari" using his id and ram's password as condition ..both conditions are necessary for security purpose


hope for early reply ....
thanks and with regards Bikash

[gpl]

Hi
YES, this is easily done, * in this context means ALL fields (columns).

SELECT takes a comma separated list of fields, so try this

SELECT e.ename, 
       e.eid, 
       e.dept, 
       a.username, 
       a.password 
FROM   employee AS e 
       LEFT JOIN admin AS a 
         ON a.eid = e.eid

you see that I am prefixing the column name with the table alias, so (in the case of eid), the parser knows which eid we want.

Im not sure what you are asking in the second point above, could you explain
And the third point, you seem to be relating 2 rows, for 1 row, you would add a WHERE clause, something like this

WHERE a.username = 'username' AND
            a.password = 'password'
 

where the username and password strings are passed in as parameters

good luck
Graham

[bikashliju]

thanks sir
i got the solution for my question no 1

but as per my second question i want to use that table created by joining of two tables as a sub query

like this
[code]select * from (select e.eid,e.ename,e.dept,a.username from employee as e left join admin as a on a.eid=e.eid) where e.eid=002[\code]

is this a workable code

[gpl]

Very nearly

SELECT * 
FROM   (SELECT e.eid, 
               e.ename, 
               e.dept, 
               a.username 
        FROM   employee AS e 
               LEFT JOIN admin AS a 
                 ON a.eid = e.eid) x
WHERE  x.eid = 002  

I think you have to provide an alias for the derived table

[ck9663]

Or simply...

 
select e.eid,e.ename,e.dept, a.username 
from employee as e 
left join admin as a on a.eid=e.eid
where e.eid=002
 
 

But if you will use the result to join into another, GPL is right. Just be careful on using too much subquery...

Happy Coding!!!

~~ CK