[benhawk]

Hi,

I have two tables, one being Bookings and the other being Customers.

A booking is linked to the customer table with a Customer_ID field which is NULL by Default, and updated when a customer is assigned.

I am trying to run an SQL Query in codeigniter which pulls back all the bookings, and is joined with the customer table by Customer_ID, however the statement does not seem to work as nothing is pulled back when the join is added.

Here the statement

                $this->db->select('*');
		$this->db->from('Bookings');
		$this->db->join('Customers', 'Customers.ID = Bookings.Customer_ID');
		$query = $this->db->get();

        return $query->result();

I guess the problem must be related to the fact that many bookings don't have a customer ID to join, but I guessed it would only do it to bookings that did.

Does anyone know the correct way to this?

[Samus]

When in development mode I normally call the profiler class. It has a function that shows all database queries occuring in class/method calls.

Simply place it in anywhere in your class

$this->output->enable_profiler(TRUE);

Just make sure it's producing the right query. If you're not sure what the right query is, you can always test it in phpmyadmin.

This post has been edited by Samus: 31 January 2012 - 02:53 PM

[FizixRichard]

Well one thing that strikes me is this... this is a join:

SELECT Bookings.*, Customers.* FROM Bookings LEFT JOIN Customers on Customers.ID = Bookings.CustomerID where Bookings.Booking_ID = '$bookingid_you_want_to_retrieve'

Now consider your code a second, your customer id in the bookings table will either be a number or NULL.

If its null, forgive me if I am wrong here, but aren't you effectively doing this:

select * from Customers where ID=''

Which will it not return all customers?

Should the default not be '0' and therefore you have a "no customer" id to actually use as reference?

This post has been edited by FizixRichard: 31 January 2012 - 04:44 PM