SQL Basics

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Remember me

Go Back   Tutorialized ForumsDatabasesSQL Basics

Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
Unread Tutorialized Forums Sponsor:
Old May 19th, 2017, 12:06 PM
1wakld89 1wakld89 is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
Join Date: May 2017
Posts: 0 1wakld89 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 17 m 22 sec
Reputation Power: 0
Joined tables return all instances in table 1

Hi all,
A complete newbie and have to extract some data for a migration project. Looking for some help as my head is fried.
I have two tables :
Unit_0 which is the main data.
UnitFin_0 which is costs associated to the units.

There are numerous costs in the UnitFin table from various orders, AND the key here is that some cost lines have been marked as deleted. I do not want to return the deleted items.

My goal is to return every assetkey from the unit_0 table and also the sum the cost lines from the unitFin_0 table, BUT not the deleted costs.
*I know that some assets do not have any costs associated to them in the unitFin_0 table*.

The issue is that the query will not retrieve units that have no costs associated on the unitFin_0 table. I want the unit to be returned but give me a blank or a zero in the sum amount column.

I choose '405x2000' as an example unit because I know it has no costs. If I just use the:
WHERE unit_0.unitkey IN ( '405X2000' ) it seems to retrieve the asset (however this would also include deleted cost lines in the bigger query). If I add in the additional "only non deleted costs" i.e. AND unitFin_0.Deleted=0 the units disappear from the results.
Please can someone help this tired old fool.
Many Thanks

QUERY BELOW:_____________________

SELECT unit_0.unitkey AssetKey,

unit_0.UnitUID AssetID,

SUM(unitFin_0.BaseAmount) AssetValue

FROM MISMON.PUB.unit unit_0

LEFT JOIN MISMON.PUB.unitFin unitFin_0 ON unit_0.unitkey = unitFin_0.unitkey

WHERE unit_0.unitkey IN ( '405X2000' ) AND unitFin_0.Deleted=0

GROUP BY unit_0.unitkey ,


Reply With Quote

Viewing: Tutorialized ForumsDatabasesSQL Basics > Joined tables return all instances in table 1

Developer Shed Advertisers and Affiliates

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 

Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.

© 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap