SQL Left Join Issues.. - 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

SQL Left Join Issues..

Author

Topic

rylan020
Starting Member

3 Posts

Posted - 2014-03-27 : 13:52:35

I have the following two tables. The first one “Run Stops” is a list of different stops we have on one of our schedules for our transportation company. The second, “Relational – Run Revenue”, is a list of tickets, with their arrivals and departures.

Route Stop SCHEDSTOP
112 TEXARKANA, AR 1
112 MENA, AR 2
112 FORT SMITH, AR 3
112 FAYETTEVILLE, AR 4
112 ROGERS BENTO, AR 5
112 JOPLIN, MO 6
112 KANSAS CITY, MO 7

Date Departure Dep# Arrival Arr#
8/1/2013 TEXARKANA, AR 1 FORT SMITH, AR 3
8/1/2013 TEXARKANA, AR 1 FORT SMITH, AR 3
8/1/2013 TEXARKANA, AR 1 FAYETTEVILLE, AR 4
8/1/2013 TEXARKANA, AR 1 FAYETTEVILLE, AR 4
8/1/2013 TEXARKANA, AR 1 FAYETTEVILLE, AR 4
8/1/2013 TEXARKANA, AR 1 JOPLIN, MO 6
8/1/2013 TEXARKANA, AR 1 JOPLIN, MO 6
8/1/2013 TEXARKANA, AR 1 JOPLIN, MO 6
8/1/2013 TEXARKANA, AR 1 KANSAS CITY, MO 7
8/1/2013 TEXARKANA, AR 1 KANSAS CITY, MO 7
8/1/2013 TEXARKANA, AR 1 KANSAS CITY, MO 7
8/1/2013 TEXARKANA, AR 1 KANSAS CITY, MO 7
8/1/2013 TEXARKANA, AR 1 KANSAS CITY, MO 7
8/1/2013 TEXARKANA, AR 1 KANSAS CITY, MO 7
8/1/2013 TEXARKANA, AR 1 KANSAS CITY, MO 7
8/1/2013 TEXARKANA, AR 1 KANSAS CITY, MO 7
8/1/2013 TEXARKANA, AR 1 KANSAS CITY, MO 7
8/1/2013 TEXARKANA, AR 1 KANSAS CITY, MO 7
8/1/2013 TEXARKANA, AR 1 KANSAS CITY, MO 7
8/1/2013 TEXARKANA, AR 1 KANSAS CITY, MO 7
8/1/2013 TEXARKANA, AR 1 KANSAS CITY, MO 7
8/1/2013 TEXARKANA, AR 1 KANSAS CITY, MO 7
8/1/2013 FORT SMITH, AR 3 FAYETTEVILLE, AR 4
8/1/2013 FORT SMITH, AR 3 FAYETTEVILLE, AR 4
8/1/2013 ROGERS BENTO, AR 5 KANSAS CITY, MO 7
8/1/2013 JOPLIN, MO 6 KANSAS CITY, MO 7
8/1/2013 JOPLIN, MO 6 KANSAS CITY, MO 7
8/1/2013 JOPLIN, MO 6 KANSAS CITY, MO 7
8/1/2013 JOPLIN, MO 6 KANSAS CITY, MO 7
8/1/2013 JOPLIN, MO 6 KANSAS CITY, MO 7
8/1/2013 JOPLIN, MO 6 KANSAS CITY, MO 7
8/1/2013 JOPLIN, MO 6 KANSAS CITY, MO 7
8/1/2013 JOPLIN, MO 6 KANSAS CITY, MO 7

My goal for the query is to show total ridership on each stop, and not just where each person got on and off, like this:
Date Schedule Stop # Load
8/1/2013 112 TEXARKANA, AR 1 22
8/1/2013 112 MENA, AR 2 22
8/1/2013 112 FORT SMITH, AR 3 22
8/1/2013 112 FAYETTEVILLE, AR 4 19
8/1/2013 112 ROGERS BENTO, AR 5 20
8/1/2013 112 JOPLIN, MO 6 25
8/1/2013 112 KANSAS CITY, MO 7 0
8/2/2013 112 TEXARKANA, AR … …
8/2/2013 112 MENA, AR … …
8/2/2013 112 FORT SMITH, AR … …

I wrote the following SQL and it returned this:

SELECT
RS."Route""Schedule",
RS."Stop""Stop",
RS."SCHEDSTOP""#",
RR."Date""Date",
COUNT(IF(RS."SCHEDSTOP">=RR."Dep#" AND RS."SCHEDSTOP"<RR."Dep#",1,0))"Load"
FROM "Run Stops" RS
LEFT JOIN "Relational - Run Revenue" RR ON RS."Route"=RR."Schedule" AND RS."SCHEDSTOP"=RR."Dep#"
GROUP BY RS."Route", RS."Stop", RS."SCHEDSTOP",RR."Date"

Date Schedule Stop # Load
8/1/2013 112 TEXARKANA, AR 1 22
8/1/2013 112 FORT SMITH, AR 3 2
8/1/2013 112 ROGERS BENTO, AR 5 1
8/1/2013 112 JOPLIN, MO 6 8

Any idea why it’s only showing the departure counts and stops, and not abiding by the left join?

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-03-27 : 14:58:12

the only problem i see is with your [Load] expression.
the count will be the same whether your IF expression resolves to 1 or 0. Change COUNT to SUM.

For the "not abiding by left join" you'll need to post actual DDL and DML. This data is obviously dummied up because your SELECT items don't match up with your actual results.

Be One with the Optimizer
TG

rylan020
Starting Member

3 Posts

Posted - 2014-03-27 : 15:36:38

Thanks for the reply,

This is the actual data output I received. I use Zoho Reports through work, and writing queries in zoho is the only familiarity I have with SQL. Zoho works much different than other programs in that you import .CSV tables, and write SQL statements like I have above. What you see is a copy/paste of an excerpt of my ticket data, an excerpt of the schedules, sql statement and an excerpt of the output.

I was hoping for the output to mimic this one:

Date Schedule Stop # Load
8/1/2013 112 TEXARKANA, AR 1 22
8/1/2013 112 MENA, AR 2 22
8/1/2013 112 FORT SMITH, AR 3 22
8/1/2013 112 FAYETTEVILLE, AR 4 19
8/1/2013 112 ROGERS BENTO, AR 5 20
8/1/2013 112 JOPLIN, MO 6 25
8/1/2013 112 KANSAS CITY, MO 7 0

Where [Load] shows who was on the bus along each stop of the schedule by counting all records where [table2.Dep#]<=[table1.SCHEDSTOP] and [table2.Arr#]>[Table1.SCHEDSTOP]. Note each record in Table 1 would be counted more than once, which is why there are only 33 records in table 2 but my intended output for [Load] would add up to 108. Is there a different way to accomplish this?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources