Solved

MYSQL - left join problem

Posted on 2011-02-22
6
427 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:GPB1983
  • 4
  • 2
6 Comments
 
LVL 4

Expert Comment

by:ziycon
ID: 34951315
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
 

Author Comment

by:GPB1983
ID: 34951425
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
 
LVL 4

Accepted Solution

by:
ziycon earned 500 total points
ID: 34951488
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:GPB1983
ID: 34951567
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
 

Author Comment

by:GPB1983
ID: 34951636
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
 

Author Closing Comment

by:GPB1983
ID: 34951792
top marks, straight tot the point, EE needs more like him
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
php mysql if then statement syntax 4 41
MYSQL simple update statement 3 54
Excel - SQL export question 3 42
FrontEnd tools to create web database application 7 61
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question