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
GPB1983Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ziyconConnect With a Mentor Commented:
I'm using the below query but both this one and the first one work grand form me with the tables you have given before on mySQL.

SELECT tt1.id, tt1.username, tt1.password, tt1.rank FROM testtable1 tt1, testtable2 tt2 WHERE tt1.id = tt2.id AND tt2.id <> 0;

How are you running these queries?
0
 
ziyconCommented:
Try something like:

SELECT tt1.id, tt1.username, tt1.password, tt1.rank FROM testtable1 tt1, testtable2 tt2 WHERE tt1.id = tt2.id AND tt2.id > 0;
0
 
GPB1983Author Commented:
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
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
GPB1983Author Commented:
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
0
 
GPB1983Author Commented:
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
0
 
GPB1983Author Commented:
top marks, straight tot the point, EE needs more like him
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.