Help with a table join

[poleposters]

Hi,

 

I need help with a join. This is what it looks like so far.

 

SELECT * FROM  users 

LEFT JOIN short_description ON users.business_id=short_description.business_id

LEFT JOIN photosON short_description.business_id= photos.business_id

LEFT JOIN location ON photos.business_id=location.business_id

LEFT JOIN services ON location.business_id=services.business_id

LEFT JOIN contact ON services.business_id=contact.business_id  WHERE business_type=1

 

This query works perfectly fine. However, the table photos might not have have a record for that particular business id. ie, it is not compulsory to have a photo. When this occurs, no rows at all are returned for that business id.

 

Is there another way of joining these tables to return the rest of the data when there is no photo record?

 

Thanks.

[cags]

I believe a LEFT JOIN will always return rows in the left column even if there is no match in the right column. For that reason this part...

 

LEFT JOIN photos ON short_description.business_id= photos.business_id 

 

...should be fine, the problem as far as I can tell is that you 'chain' the JOINs meaning the next line of code is reliant on this one finding a value in both columns which it might not. Since you are always joining on the same field in each table, I'd recommend always joining the extra tables against the original table.

 

SELECT * FROM users u
LEFT JOIN short_description sd ON u.business_id=sd.business_id 
LEFT JOIN photos p ON u.business_id= p.business_id 
LEFT JOIN location l ON u.business_id=l.business_id 
LEFT JOIN services s ON u.business_id=s.business_id 
LEFT JOIN contact c ON u.business_id=c.business_id  WHERE business_type=1

 

[kickstart]

Hi

 

Think the chaining should be fine (in that you should still get a row back, although most of the columns could be null), but only if business_type is on the users or short_description tables.

 

All the best

 

Keith

[cags]

I've just noticed the code I've posted didn't save the edit I made, that is odd... anyway the first single letter on each row should have been u, I noticed that when I click preview, edited it and clicked submit, but it's not saved the edit. As you say though Keith it should still return a row, it just wouldn't have most of the information I believe the OP wants. The main caveat being they didn't actually specify a table for business_type, but I assume it's the users table. Does MySQL default to the original table when one isn't specified?

[kickstart]

Hi

 

You only need to specify the table for a column where the column is on more than one table in the SELECT.

 

By the way, the u's appear to be there for me.

 

All the best

 

Keith

[cags]

You only need to specify the table for a column where the column is on more than one table in the SELECT.

That makes sense. With regards to the u's, I'm very confused now I might go back to bed I think my man flu has come out for the latest round without it's gloves on.

[kickstart]

Hi

 

It is just the random error generator that is kicking in. . Years back on a mainframe I had a program that worked fine with debugging stuff in place (PUT SKIP LIST statements, about equivalent to ECHO), but fell over when you took them out. That was a real pain to fix.

 

All the best

 

Keith