GPB1983
asked on
MYSQL - left join problem
Hi,
I am trying to create an application in php to create an SQL join, I can code the application, but I am having problems creating the string:
the SQL syntax is where the problem is: the SQL I have is:
SELECT id, username, password, rank FROM TESTTABLE1 LEFT JOIN TESTTABLE2 ON TESTTABLE1.id = TESTTABLE2.id WHERE TESTTABLE2.id > 0
the table structure is as follows:
TESTTABLE1
id username password status rank
1 gavin 12345bbc limited low
2 david 456456fg limited low
TESTTABLE2
id username
1 gavin
2 david
please can someone explain where it is that I am going wrong, thanks
Gavin
I am trying to create an application in php to create an SQL join, I can code the application, but I am having problems creating the string:
the SQL syntax is where the problem is: the SQL I have is:
SELECT id, username, password, rank FROM TESTTABLE1 LEFT JOIN TESTTABLE2 ON TESTTABLE1.id = TESTTABLE2.id WHERE TESTTABLE2.id > 0
the table structure is as follows:
TESTTABLE1
id username password status rank
1 gavin 12345bbc limited low
2 david 456456fg limited low
TESTTABLE2
id username
1 gavin
2 david
please can someone explain where it is that I am going wrong, thanks
Gavin
ASKER
hiya, thanks for the quick reply,
I have tried your solution and it generates nothing what so ever.
the syntax I have used is:
SELECT TESTTABLE1.id, TESTTABLE1.username, TESTTABLE1.password, TESTTABLE1.rank FROM TESTTABLE1, TESTTABLE2 WHERE TESTTABLE1.id = TESTTABLE2.id AND tt2.id
and the DB engine is InnoDB (supports foreign keys)
where am I going wrong please?
regards, Gavin
I have tried your solution and it generates nothing what so ever.
the syntax I have used is:
SELECT TESTTABLE1.id, TESTTABLE1.username, TESTTABLE1.password, TESTTABLE1.rank FROM TESTTABLE1, TESTTABLE2 WHERE TESTTABLE1.id = TESTTABLE2.id AND tt2.id
and the DB engine is InnoDB (supports foreign keys)
where am I going wrong please?
regards, Gavin
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi, thanks it works, well sort of:
it gives me some results, but only the contents of testtable1, I thought that this query would basically give me the contents of both tables.
thanks,
regards, Gavin
it gives me some results, but only the contents of testtable1, I thought that this query would basically give me the contents of both tables.
thanks,
regards, Gavin
ASKER
false alarm mate, I saw where I was going wrong.
thanks for the help, EE needs more people like you!
top marks all round for ya!
regards, Gavin
thanks for the help, EE needs more people like you!
top marks all round for ya!
regards, Gavin
ASKER
top marks, straight tot the point, EE needs more like him
SELECT tt1.id, tt1.username, tt1.password, tt1.rank FROM testtable1 tt1, testtable2 tt2 WHERE tt1.id = tt2.id AND tt2.id > 0;