If you have all the selects individually and then table.* that is probably your problem.
SELECT *
FROM
cart
LEFT OUTER JOIN dkb
ON cart.id = dkb.id
LEFT OUTER JOIN cdkb
on cart.id = cdkb.id
LEFT OUTER JOIN double
on cart.id = double.id
WHERE
cart.cookieId = GetCartId() AND cdkb.id IS NOT NULL
To set up some data for this query for MySQL:
-- **********************************************************************
-- Table holding all the items in the users shopping cart, along with
-- a cookieId for thier session
-- **********************************************************************
Create table cart
(
cartId integer,
cookieId integer,
id integer,
qty integer
)
go
-- **********************************************************************
-- Table all the items available for sale with the price
-- **********************************************************************
Create table dkb
(
id integer,
name varchar(20),
price decimal(12,2)
)
go
-- **********************************************************************
-- Table that looks like the items table, but we have no idea why it is
-- here.
-- **********************************************************************
Create table cdkb
(
id integer,
name varchar(20),
price decimal(12,2)
)
go
-- **********************************************************************
-- Another table with a price in it .. a completely different id field
-- and a variety ... WTH is it for - No idea.
-- **********************************************************************
Create table dblv
(
dbl_id integer,
price decimal(12,2),
variety varchar(20)
)
go
insert into cart (cartid, cookieid, id, qty) values (1, 1, 1, 1);
go
insert into cart (cartid, cookieid, id, qty) values (1, 1, 2, 1);
go
insert into cart (cartid, cookieid, id, qty) values (1, 1, 3, 1);
go
insert into cart (cartid, cookieid, id, qty) values (1, 1, 4, 1);
go
insert into cart (cartid, cookieid, id, qty) values (1, 1, 5, 1);
go
insert into cart (cartid, cookieid, id, qty) values (1, 1, 6, 1);
go
insert into cart (cartid, cookieid, id, qty) values (1, 1, 7, 1);
go
insert into cart (cartid, cookieid, id, qty) values (1, 1, 8, 1);
go
insert into dkb (id, name, price) values (1,'my dkb 1', 10.00);
go
insert into dkb (id, name, price) values (2,'my dkb 2', 20.00);
go
insert into dkb (id, name, price) values (3,'my dkb 3', 30.00);
go
insert into dkb (id, name, price) values (4,'my dkb 4', 40.00);
go
insert into cdkb (id, name, price) values (5,'my cdkb 5', 50.00);
go
insert into cdkb (id, name, price) values (6,'my cdkb 6', 60.00);
go
insert into cdkb (id, name, price) values (7,'my cdkb 7', 70.00);
go
insert into cdkb (id, name, price) values (8,'my cdkb 8', 80.00);
go
insert into dblv (dbl_id, price, variety) values (1,1.99,'my dbl 1 variety');
go
insert into dblv (dbl_id, price, variety) values (2,1.99,'my dbl 2 variety');
go
then the results of this query:
SELECT
cart.id cart_id,
dkb.id dkb_id,
cdkb.id cdkb_id,
cart.*,
dkb.*,
cdkb.*,
dblv.*
FROM
cart
LEFT OUTER JOIN dkb
ON cart.id = dkb.id
LEFT OUTER JOIN dblv
on dkb.id = dblv.dbl_id
LEFT OUTER JOIN cdkb
on cart.id = cdkb.id
results show up as:
cart_id dkb_id cdkb_id cartId cookieId id qty id name price id name price dbl_id price variety
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------- ------------ ---------- -------------------- ------------ ---------- ------------ --------------------
1 1 NULL 1 1 1 1 1 my dkb 1 10.00 NULL NULL NULL 1 1.99 my dbl 1 variety
2 2 NULL 1 1 2 1 2 my dkb 2 20.00 NULL NULL NULL 2 1.99 my dbl 2 variety
3 3 NULL 1 1 3 1 3 my dkb 3 30.00 NULL NULL NULL NULL NULL NULL
4 4 NULL 1 1 4 1 4 my dkb 4 40.00 NULL NULL NULL NULL NULL NULL
5 NULL 5 1 1 5 1 NULL NULL NULL 5 my cdkb 5 50.00 NULL NULL NULL
6 NULL 6 1 1 6 1 NULL NULL NULL 6 my cdkb 6 60.00 NULL NULL NULL
7 NULL 7 1 1 7 1 NULL NULL NULL 7 my cdkb 7 70.00 NULL NULL NULL
8 NULL 8 1 1 8 1 NULL NULL NULL 8 my cdkb 8 80.00 NULL NULL NULL
sorry not much time today - long day at work. :-) I'll check back tomorrow ..