CODEWITHSUNDEEP
mysql
Will Case
Will Case

Reputation: 105

Full JOIN MySQL Query is returning empty

So here is a MySQL Query:

SELECT TestSite . * , LoggedCarts . * 
FROM TestSite, LoggedCarts
WHERE TestSite.email =  'LoggedCarts.Bill-Email'
LIMIT 0 , 30

It is returning an empty result set, when it should be returning four results based on the tables below.

First Table: LoggedCarts - Column: Bill-Email

[email protected]
[email protected]

Second Table: TestSite - Column: email

[email protected]
[email protected]
[email protected]
[email protected]
[email protected]

The goal is to get a MySQL statement that returns the rows in Table: TestSite that don't match the rows in Table: LoggedCarts.

Note: I understand that the use of a hyphen in a column name requires special care when constructing a query, involving backticks to tell MySQL there are special characters. I would change the column names to match up, however the Table: LoggedCarts has data fed via post from a Yahoo Shopping Cart and without heavy preparation before insertion setting the name to anything but the key sent in the post data is daunting.

However, if it turns out rebuilding the data prior to insertion is easier than using a JOIN statement or for some reason using two columns with different names as the comparison columns just doesn't work, I will go through and rebuild the database and PHP code.

Upvotes: 0

Views: 98

Answers (2)

Will Case
Will Case

Reputation: 105

With a bit of research inspired by somne of the hints given, I found the solution I was looking for here: SELECT * WHERE NOT EXISTS

Does exactly what I need it to do, and as a bonus, I like the shorthand syntax that is used that allows you to put in an alias for the table name and use the alias throughout the statement.

SELECT *
FROM TestSite e
WHERE NOT EXISTS
    (
    SELECT  null 
    FROM    LoggedCarts d
    WHERE   d.`Bill-Email` = e.email
    )

Upvotes: 0

depquid
depquid

Reputation: 776

Single quotes indicate a string literal. You need to use backticks for identifiers. Also, each component of an identifier must be quoted individually.

SELECT TestSite . * , LoggedCarts . * 
FROM TestSite, LoggedCarts
WHERE TestSite.email =  LoggedCarts.`Bill-Email`
LIMIT 0 , 30

From the manual:

If any components of a multiple-part name require quoting, quote them individually rather than quoting the name as a whole. For example, write `my-table`.`my-column`, not `my-table.my-column`.

Upvotes: 3

Related Questions