Full JOIN MySQL Query is returning empty

Question

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.

You've potentially answered your own question ...special care when constructing a query, involving backticks to tell MySQL there are special characters. — MonkeyZeus
– MonkeyZeus, Commented Dec 11, 2013 at 20:08
Removed the extra category tags. Backticks around the column identifier resulted in an unknown column error, whereas apostrophes resulted in the join occuring but all of the LoggedCart table values were NULL. — Will Case
– Will Case, Commented Dec 11, 2013 at 20:22
FROM TestSite, LoggedCarts is not a "full join" (which is the same as "full outer join" - something MySQL can not do) - it's a cross join. — user330315
– user330315, Commented Dec 11, 2013 at 21:00

depquid · Accepted Answer · 2013-12-11 20:32:47Z

3

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`.

edited Dec 11, 2013 at 20:32

answered Dec 11, 2013 at 20:08

depquid

7967 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Will Case Over a year ago

The backticks resulted in an Unknown column error. Apostrophes allow the join to happen, but the values from LoggedCarts are all NULL.

Will Case Over a year ago

That did it, now I just have to reverse it with a != or <> operator. Thank you!

Community · Accepted Answer · 2017-05-23 11:52:30Z

0

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
    )

edited May 23, 2017 at 11:52

CommunityBot

11 silver badge

answered Dec 11, 2013 at 20:47

Will Case

1058 bronze badges

Collectives™ on Stack Overflow

Full JOIN MySQL Query is returning empty

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related