Help with query

[imperium2335]

Hi,

 

I keep getting 'Unknown column 'entity_contacts.id' in 'on clause'' in my code:

 

SELECT entity_details.name, entity_contacts.name AS cName, emailT.email, telT.tel, countryTable.country, entity_contacts.id
FROM entity_details, entity_contacts, (SELECT countries.country FROM countries, entity_details WHERE countries.id = entity_details.countryRef) AS countryTable
LEFT JOIN entity_contacts_emails AS emailT ON emailT.contactRef = entity_contacts.id
LEFT JOIN entity_contacts_telephones AS telT ON telT.contactRef = entity_contacts.id
WHERE entity_contacts.isPrimary = 1
AND entity_contacts.entityRef = entity_details.id
AND entity_details.ownerRef = 1

 

Where is it wrong?

[Muddy_Funster]

moslt likely there is no column called "id" in the entity_contacts table.

 

lets see your table structure if that's not the problem.

[imperium2335]

CREATE TABLE IF NOT EXISTS `entity_contacts` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `entityRef` int(11) NOT NULL,

  `contactTypeRef` int(2) NOT NULL COMMENT 'Engineer, finance etc',

  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,

  `twitter` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,

  `facebook` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,

  `linkedIn` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,

  `isPrimary` tinyint(1) NOT NULL,

  PRIMARY KEY (`id`),

  KEY `customerRef` (`entityRef`),

  KEY `isPrimary` (`isPrimary`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7841 ;

 

[imperium2335]

I think it's because my JOINs are in the wrong place?

[Muddy_Funster]

to be honest there is nothing obviously wrong here,  All I have done is add some parenthesis and put in an inner join instead of comparing the fields in the WHERE clause, but it looks ok other than that.

 

SELECT entity_details.name, entity_contacts.name AS cName, emailT.email, telT.tel, countryTable.country, entity_contacts.id
FROM 
(SELECT countries.country FROM countries, entity_details WHERE countries.id = entity_details.countryRef) AS countryTable,
entity_details
INNER JOIN entity_contacts ON (entity_contacts.entityRef = entity_details.id)
LEFT JOIN entity_contacts_emails AS emailT ON (emailT.contactRef = entity_contacts.id)
LEFT JOIN entity_contacts_telephones AS telT ON (telT.contactRef = entity_contacts.id)
WHERE ((entity_contacts.isPrimary = 1)
(AND entity_details.ownerRef = 1))

[imperium2335]

Thanks,

 

Whats the difference between and INNER JOIN and a LEFT JOIN?

[kickstart]

Hi

 

INNER JOIN brings back a row when there are matching rows on both tables. LEFT OUTER JOIN brings back a row even if there is no matching row on the right hand table.

 

For example, say you had a table of people, and a table of cars. Using an INNER JOIN you would join the 2 tables together to get a list of people who had cars, with someone who had 2 cars appearing twice in the list but someone who didn't have a car wouldn't be on the list. Using a LEFT OUTER JOIN you could include the people without a car, with the car details being null on the row brought back about them

 

All the best

 

Keith

[imperium2335]

Thanks.

 

I think I was right in choosing LEFT JOIN then, because I'd want a result to show up even if certain information for a contact is missing, such as telephone etc.

[Muddy_Funster]

your LEFT JOINS are still there, I added an INNER JOIN to replace the line in your WHERE clause that reads

AND entity_contacts.entityRef = entity_details.id

[kickstart]

I think I was right in choosing LEFT JOIN then, because I'd want a result to show up even if certain information for a contact is missing, such as telephone etc.

 

An INNER JOIN would still bring back a matching row with blank fields. What it won't do (and why you use an OUTER JOIN) is bring back a row when there is no matchig JOINed row.

 

All the best

 

Keith