0 Count possible? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

0 Count possible?

Author

Topic

ccdavies
Starting Member

11 Posts

Posted - 2012-02-11 : 11:45:33

Hi.
I have managed to create the code below to display the amount of entries created by a single author related to a specific booking id.


SELECT COUNT(relationships.rel_parent_id) as bookings_completed, titles.entry_id, titles.author_id, titles.channel_id, order_items.entry_id, order_items.order_id, order_items.title, order_items.quantity, relationships.rel_id, relationships.rel_parent_id as rel_order_id, .relationships.rel_child_id as rel_booking_id, members.screen_name  
FROM exp_channel_titles AS titles
LEFT JOIN exp_cartthrob_order_items AS order_items ON titles.entry_id = order_items.order_id
LEFT JOIN exp_relationships AS relationships ON relationships.rel_child_id = order_items.order_id
LEFT JOIN exp_members AS members ON members.member_id = titles.author_id
WHERE titles.channel_id = 10

The problem is, this displays how many, but if there aren't any entries then it doesn't display anything. I want any order created that doesn't have any entries to display 0.
I think this is mostly due to my join here:

LEFT JOIN exp_relationships AS relationships ON relationships.rel_child_id = order_items.order_id

Could anyone please show me how I can test to see if there are any entries, and then display the amount, including a 'none' or zero count?

Thank you

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-11 : 12:40:21

May be this?

SELECT COUNT(relationships.rel_parent_id) AS bookings_completed,
       titles.entry_id,
       titles.author_id,
       titles.channel_id,
       order_items.entry_id,
       order_items.order_id,
       order_items.title,
       order_items.quantity,
       relationships.rel_id,
       relationships.rel_parent_id AS rel_order_id,
       .relationships.rel_child_id AS rel_booking_id,
       members.screen_name
FROM   exp_cartthrob_order_items AS order_items
       LEFT JOIN exp_channel_titles AS titles
            ON  titles.entry_id = order_items.order_id  AND titles.channel_id = 10
       LEFT JOIN exp_relationships AS relationships
            ON  relationships.rel_child_id = order_items.order_id
       LEFT JOIN exp_members AS members
            ON  members.member_id = titles.author_id
--WHERE  titles.channel_id = 10

That will pick up rows in order_items even when there are no titles for that order_item.

ccdavies
Starting Member

11 Posts

Posted - 2012-02-11 : 13:05:09

Hi. Thank you for the reply.

To test I have three orders, one with entries.

With the changes you made it returns 1 result, with no entries. However there is two?

Is there any way I can display the number of entries, so something like 0, 1 or 2? Rather than just those without or with?

Thank very much!!

ccdavies
Starting Member

11 Posts

Posted - 2012-02-11 : 16:53:53

I have spent another couple of hours looking at this, and just cant figure out how I can approach this.
With my initial code, I think that by joining the relationships table like below:

LEFT JOIN exp_relationships AS relationships
            ON  relationships.rel_child_id = order_items.order_id

I am displaying results only if there is a relationship between it and the order.
What I want, is to display results regardless if there is a relationship, but if there is a relationship display how many there is.
Basically, I don't know how I can join the relationships table without setting a condition...

Please someone show me the way... I am so stuck!!

SELECT COUNT(relationships.rel_parent_id) AS bookings_completed,
       titles.entry_id,
       titles.author_id,
       titles.channel_id,
       order_items.entry_id,
       order_items.order_id,
       order_items.title,
       order_items.quantity,
       relationships.rel_id,
       relationships.rel_parent_id AS rel_order_id,
       relationships.rel_child_id AS rel_booking_id,
       members.screen_name
FROM exp_channel_titles AS titles
       LEFT JOIN exp_cartthrob_order_items AS order_items
            ON  titles.entry_id = order_items.order_id
       LEFT JOIN exp_relationships AS relationships
            ON  relationships.rel_child_id = order_items.order_id
       LEFT JOIN exp_members AS members
            ON  members.member_id = titles.author_id
WHERE  titles.channel_id = 10

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-11 : 17:20:14

If you can post three things:
1. DDL for the tables,
2. Sample input,
3. Expected output
someone on the forum would be able to suggest (and usually suggest rather quickly) how to solve the issue.

Take a look at Brett's blog to see how to get table DDL etc. Without that information, I or anyone else is looking at your join conditions and guessing - and obviously I was way off.

Brett's blog: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

ccdavies
Starting Member

11 Posts

Posted - 2012-02-12 : 04:56:49

Hi.
Okay, so I have taken a look at the blog post, and I am afraid I don't have DDL. The tables are created by my CMS Expression Engine.
However, I can give you more details. I hope that helps enough.

order_items - Table contains the order details. From this table I need the 'order_id'.

titles - Here I want to find out the author id against the entry_id. The entry_id is the same as the order_id.

relationships - If a user has created any entries related to the order, this table displays the order_id, and the entry_id of the entry they have created. The 'count' is to display how many entries are related.

So far the query display correctly IF there is a relationship. What I want is to return a 0 if the order doesn't have any related entries, but if there are related entries display as the code does currently.

So something like:

screen_name: Chris
title - Product name
quantity - 2
bookings_completed - 0 if no related entries, 1 if 1 related entry, 2 if 2 related entries.

Currently it only displays the above if the bookings_completed is 1 or more.

Does this make more sense?

I am very thankful for any guidance you can give.

Thank you
Chris

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources