I am having trouble writing an SQL statement for a shoppingcart environment that lists all pages and their items even if the page doesn't have any items. This needs to be an OUTER JOIN but I can't get it to work.

This query will say: return all pages with their items even if the page has no items.

I have four tables:

tblitems
---------
id (PK)
myid
itemid


tblpagesitems
----------------
itemid (PK) - links to tblitems.itemid
scid (PK)
spid (PK)
itemdisplayorder


tblshoppingcartpages
------------------------
scid (PK) - links to tblpagesitems.scid
spid (PK) - links to tblpagesitems.spid
heading
pagedisplayorder


tblshoppingcart
------------------
scid (PK) - links to tblpagesitems.scid

Sample data in the tables is:

tblitems
---------
id myid itemid
1 1 1
2 1 2
3 1 3

tblpagesitems
----------------
itemid scid spid itemdisplayorder
1 1 1 1
2 1 2 1
3 1 2 2

tblshoppingcartpages
------------------------
scid spid heading pagedisplayorder
1 1 headone 1
1 2 headtwo 2
1 3 headthree 3

tblshoppingcart
-----------------
scid
1

The idea is that there is one user (myid=1) who has a shoppingcart (tblshoppingcart.scid=1), which has three pages. Spread amongst these three pages are three items as follows: headone has item1, headtwo has item2 and item3, and headthree has no items assigned.

Items have a specific order on the page and the pages have an order within the shopping cart.

It is important to link tblpagesitems.scid = tblshoppingcartpages.scid and tblpagesitems.spid = tblshoppingcartpages.spid.

I want a query to return the following:

itemid scid spid heading itemdisplayorder
1 1 1 headone 1
2 1 2 headtwo 1
3 1 2 headtwo 2
null 1 3 headthree null

Any help for me to solve this would be much appreciated

tblpagesitems
----------------
itemid scid spid itemdisplayorder
1 1 1 1
2 1 2 1
3 1 2 2

tblshoppingcartpages
------------------------
scid spid heading pagedisplayorder
1 1 headone 1
1 2 headtwo 2
1 3 headthree 3


----------query is as follows
select a.itemid,a.scid,a.spid,b.heading,b.pagedisplayorde r from tblpagesitems a
right outer join tblshoppingcartpages b on a.scid=b.scid and a.spid=b.spid



urt

Help yourself by helping someone.

You have a (relatively minor) design error in your tables.

You have scid in tblpagesitems, but that is redundant information, since the spid value *implies* the correct scid by way of the tblshoppingcartpages table.

Also, scid is only a PK (Primary Key) in tblshoppingcart. It is FK (foreign key) in tblshoppingcartpages. And then both itemid and spid are FK in tblpagesitems

In a set of tables this simple, making mistakes in the designations (and having redundant fields) isn't too important. But as your DB grows, say to dozens of tables, it's a good idea to keep these things straight.

As utrivedi showed, you don't really need to link more than two tables together to get the result you *said* you wanted. But I suspect that eventually you will need to link all 4 tables. With more than 2 tables, there *ARE* minor differences in how the various DBs want you to do JOINs, so you need to tell us WHAT DB you are using. Access, for example, is especially fussy about order of joins and about using parentheses.

I am also not able to understand his database design, it seems that he designed his table for front end and not for the shopping cart. But I was just explaining how we can use outer joins.

urt

Help yourself by helping someone.

Thanks for your replies.

My reasoning for the current design is that a different shopping cart (let's say, scid=2) might have two pages, so the spid for these would be 1 and 2. Putting a bit more data into the tables would show:

tblpagesitems
----------------
itemid scid spid itemdisplayorder
1 1 1 1
2 1 2 1
3 1 2 2
1 2 1 1
2 2 2 2


tblshoppingcartpages
------------------------
scid spid heading pagedisplayorder
1 1 headone 1
1 2 headtwo 2
1 3 headthree 3
2 1 headfour 1
2 2 headfive 2

(Notice that tblpagesitems.itemid 1 and 2 are also displayed in this second shopping cart i.e. these items are now in two shopping carts.)

In this case, tblpagesitems.spid is not unique and does not therefore imply the scid in tblshoppingcartpages.

And, yes, you are right, I do need to join up the other tables. Extending tblitems a bit more I have:

tblitems
---------
id myid itemid name price liveitem
1 1 1 name1 20 1
2 1 2 name2 35 1
3 1 3 name3 12 1

My query really needs to be expanded to say "select tblitems.* ..... where myid=1".

The output should display:

itemid name price scid spid heading itemdisplayorder
1 name1 20 1 1 headone 1
2 name2 35 1 2 headtwo 1
3 name3 12 1 2 headtwo 2
null null null 1 3 headthree null

If you think my database/table structure could be improved, I would certainly consider fixing it to make it better. I have put it together myself so I accept it may be wrong.

... and I'm using MySQL.