Query with JOINS

[imperium2335]

Hi,

 

I'm trying to a query to check whether a record has a reference in two other tables, I have the following:

 

Explain SELECT COUNT(*) AS new FROM entity_details
						 	LEFT JOIN entity_turned_prospect
							ON entity_details.id = entity_turned_prospect.entityRef
							LEFT JOIN entity_turned_customer
							ON entity_details.id = entity_turned_customer.entityRef
						 WHERE entity_details.ownerRef = 41
		  				 AND entity_details.typeRef = 4
						 AND entity_details.dateCreated = MONTH(NOW())
		  				 AND entity_turned_prospect.id = NULL
						 AND entity_turned_customer.id = NULL

 

But I get 0 results returned when I know for a fact there should be some.

 

If I change the count to just select * I then get an error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS new FROM entity_details, LEFT JOIN entity_turned_prospect ON' at line 1

 

Could someone show me how to fix this?

[Adam]

Did you also remove AS new? You can't set a single alias to every column.

[imperium2335]

Tried it, still nothing comes back.

[imperium2335]

Sorry, it should be:

 

Explain SELECT COUNT(*) FROM entity_details
						 	LEFT JOIN entity_turned_prospect
							ON entity_details.id = entity_turned_prospect.entityRef
							LEFT JOIN entity_turned_customer
							ON entity_details.id = entity_turned_customer.entityRef
						 WHERE entity_details.ownerRef = 41
		  				 AND entity_details.typeRef = 4
						 AND MONTH(entity_details.dateCreated) = MONTH(NOW())
		  				 AND entity_turned_prospect.id = NULL
						 AND entity_turned_customer.id = NULL

[Adam]

I would run the query through your DBMS with no WHERE condition, and then add them back in one at a time till you find the culprit.

[imperium2335]

Hi thanks for your help so far.

 

I have found what's wrong, I need to give my joined columns aliases.

 

How do I do that?

 

Cheers.

[Adam]

LEFT JOIN entity_turned_prospect AS alias_name

 

Or..

 

LEFT JOIN entity_turned_prospect alias_name

 

"AS" is optional.

[imperium2335]

I tried that but that still gives me the same column names as the main table.

 

I.e. my table has 3 columns called id and 2 columns called entityRef.

 

It is the lines :

 

entity_turned_prospect.id = NULL

AND entity_turned_customer.id = NULL

 

That are causing no results.

 

I want these statements to only apply to the joined tables and not the main table, because according to these two lines those columns will never be null.

 

Do you know what I mean?

[imperium2335]

I've just tried:

 

SELECT
(SELECT entityRef AS a1 FROM entity_turned_prospect) AS t1,
(SELECT entityRef AS b1 FROM entity_turned_customer) AS t2
FROM entity_details
LEFT JOIN t1
ON entity_details.id = t1.a1
LEFT JOIN t2
ON entity_details.id = t2.b1
WHERE entity_details.ownerRef = 41
AND entity_details.typeRef = 4
AND MONTH(entity_details.dateCreated) = MONTH(NOW())
#AND entity_turned_prospect.id = NULL	
#AND entity_turned_customer.id = NULL

 

But tells me that table t1 doesn't exists.

 

I feel this is closer to what I want just need to understand why this isn't working now.

[Adam]

Remove the sub-queries. Checking for null values you should use "IS NULL":

 

AND IS NULL entity_turned_prospect.id
AND IS NULL entity_turned_customer.id

[imperium2335]

Thanks for your help.

 

I just tried a different method and it has worked.

 

SELECT entity_details.id
						FROM entity_details
						WHERE entity_details.id NOT IN (SELECT entityRef FROM entity_turned_prospect)
						AND entity_details.id NOT IN (SELECT entityRef FROM entity_turned_customer)
						AND MONTH(entity_details.dateCreated) = MONTH(NOW())
						AND entity_details.typeRef = 4
						AND entity_details.ownerRef = ?

 

Thanks for your insights!