createing a query with optional joins

[showman13]

Hopefully this will make sense and there is a solution in a single query... if not, I'm sure i can do it using multiple queries.

I wrote a query that I thought would work, but didn't take into consideration that some of the records won't have data coming from one of the tables... That creates a problem, because it doesn't bring back any data for those records at all...

Here is my query

SELECT i.item_id, i.product_id, i.qty, i.price_each, bc.card_back, bc.full_name, p.product_name
  FROM dealer_item AS i, dealer_product as p, BC_profile as bc
  WHERE i.cart_id='2'
  AND i.status='A'
  AND p.product_id=i.product_id
  AND i.profile_id=bc.profile_id

it is basically getting ALL items in a shopping cart...

The problem is that if the item happens to be Business Cards, there are a couple of pieces of data that need to come from the BC_profile table, and any other products ordered won't have any records in that table.

So, the question is... can a single query selectively get the two fields from the BC_profile table, based on the first 2 characters of the i.product_id being 'BC'?

Or is that asking a lot from the query?

Any feedback would be appreciated (preferably positive).

thanks in advance.
douglas

[|-|x]

Have a read of the MySQL Join documentation, specifically the section that covers Left/Right joins.

What I would use here is a LEFT JOIN which allows the parent record (ie: dealer_item) to be shown with a null record where no data exists in the child record (dealer_product). More left join can be used to add more "optional" child tables to the query in the same way.

Have a go at rewriting the joins and I can help further if you have trouble with syntax or anything.

[showman13]

Thank you for your response...

I will do some more studying...