table 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

table issues

Author

Topic

midpenntech
Posting Yak Master

137 Posts

Posted - 2009-01-20 : 14:30:15

Hello I have a issue with pulling data from tables. What I am currenlty pulling is correct using this code. What I am having an issue is it pulls all this data but when i use the where (custsnum=000) it removes 2 items. that should still be on the list. those items have NULL where a custsnum would be. Meaning they are not in that table. How can i keep those 2 items in the data and keep the custsnum=000. Any help would be good.

SELECT eqdailyinv.kequipnum, eqdailyinv.kmfg, eqdailyinv.kmodel, eqdailyinv.eqpsldcust, eqdailyinv.eqpslddate, eqdailyinv.eqpphybr, eqdailyinv.custins, custmast.custname, repmast.custname AS repname, GPCOSTBYINV.cost, CASE WHEN sell <> 0 THEN (sell - COALESCE (cost, 0)) END AS Grosspro,GPCOSTBY61000.sell

FROM GPCOSTBY61000 LEFT OUTER JOIN GPCOSTBYINV ON GPCOSTBY61000.kequipnum = GPCOSTBYINV.kequipnum RIGHT OUTER JOIN
eqdailyinv ON GPCOSTBYINV.kequipnum = eqdailyinv.kequipnum LEFT OUTER JOIN custmast ON eqdailyinv.eqpsldcust = custmast.kcustnum LEFT OUTER JOIN repmast ON eqdailyinv.custins = repmast.custslsmn

WHERE (eqdailyinv.eqpslddate BETWEEN @fromdate AND @thrudate) AND (eqdailyinv.eqpsldamt <> 0) AND (eqdailyinv.kmfg = 'cd')

ORDER BY eqdailyinv.kequipnum

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-20 : 14:42:50

[code]SELECT eqdailyinv.kequipnum,
eqdailyinv.kmfg,
eqdailyinv.kmodel,
eqdailyinv.eqpsldcust,
eqdailyinv.eqpslddate,
eqdailyinv.eqpphybr,
eqdailyinv.custins,
custmast.custname,
repmast.custname AS repname,
GPCOSTBYINV.cost,
CASE
WHEN sell <> 0 THEN (sell - COALESCE (cost, 0))
END AS Grosspro,
GPCOSTBY61000.sell
FROM GPCOSTBY61000
LEFT JOIN GPCOSTBYINV ON GPCOSTBYINV.kequipnum = GPCOSTBY61000.kequipnum
RIGHT JOIN eqdailyinv ON eqdailyinv.kequipnum = GPCOSTBYINV.kequipnum
AND eqdailyinv.eqpslddate BETWEEN @fromdate AND @thrudate
AND eqdailyinv.eqpsldamt <> 0
AND eqdailyinv.kmfg = 'cd'
LEFT JOIN custmast ON custmast.kcustnum = eqdailyinv.eqpsldcust
LEFT JOIN repmast ON repmast.custslsmn = eqdailyinv.custins
ORDER BY eqdailyinv.kequipnum[/code]

E 12°55'05.63"
N 56°04'39.26"

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-20 : 14:43:22

Hello

quote:
Originally posted by midpenntech

Hello I have a issue with pulling data from tables. What I am currenlty pulling is correct using this code. What I am having an issue is it pulls all this data but when i use the where (custsnum=000) it removes 2 items.

I do not see this where clause in your query but is there a reason you could not simply add another condition to your WHERE clause to capture the data?

ie.. WHERE custsnum=000 or custsnum is null

This would return those two rows you seek I think.. Perhaps I don't fully understand the problem?

r&r

midpenntech
Posting Yak Master

137 Posts

Posted - 2009-01-20 : 14:45:14

sorry abotu that here is the correct code

SELECT eqdailyinv.kequipnum, eqdailyinv.kmfg, eqdailyinv.kmodel, eqdailyinv.eqpsldcust, eqdailyinv.eqpslddate, eqdailyinv.eqpphybr, eqdailyinv.custins,
custmast.custname, repmast.custname AS repname, GPCOSTBYINV.cost, CASE WHEN sell <> 0 THEN (sell - COALESCE (cost, 0)) END AS Grosspro,
GPCOSTBY61000.sell
FROM GPCOSTBY61000 INNER JOIN
GPCOSTBYINV ON GPCOSTBY61000.kequipnum = GPCOSTBYINV.kequipnum RIGHT OUTER JOIN
eqdailyinv ON GPCOSTBYINV.kequipnum = eqdailyinv.kequipnum LEFT OUTER JOIN
custmast ON eqdailyinv.eqpsldcust = custmast.kcustnum LEFT OUTER JOIN
repmast ON eqdailyinv.custins = repmast.custslsmn
WHERE (eqdailyinv.eqpslddate BETWEEN @fromdate AND @thrudate) AND (custmast.custsnum = '000') AND (eqdailyinv.eqpsldamt <> 0)
ORDER BY eqdailyinv.kequipnum

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-20 : 14:52:27

[code]SELECT eqdailyinv.kequipnum,
eqdailyinv.kmfg,
eqdailyinv.kmodel,
eqdailyinv.eqpsldcust,
eqdailyinv.eqpslddate,
eqdailyinv.eqpphybr,
eqdailyinv.custins,
custmast.custname,
repmast.custname AS repname,
GPCOSTBYINV.cost,
CASE
WHEN sell <> 0 THEN (sell - COALESCE (cost, 0))
END AS Grosspro,
GPCOSTBY61000.sell
FROM GPCOSTBY61000
LEFT JOIN GPCOSTBYINV ON GPCOSTBYINV.kequipnum = GPCOSTBY61000.kequipnum
RIGHT JOIN eqdailyinv ON eqdailyinv.kequipnum = GPCOSTBYINV.kequipnum
AND eqdailyinv.eqpslddate BETWEEN @fromdate AND @thrudate
AND eqdailyinv.eqpsldamt <> 0
AND eqdailyinv.kmfg = 'cd'
LEFT JOIN custmast ON custmast.kcustnum = eqdailyinv.eqpsldcust
AND custmast.custsnum = '000'
LEFT JOIN repmast ON repmast.custslsmn = eqdailyinv.custins
ORDER BY eqdailyinv.kequipnum[/code]

E 12°55'05.63"
N 56°04'39.26"

midpenntech
Posting Yak Master

137 Posts

Posted - 2009-01-20 : 14:55:02

what ever you did just took out all the sell cost made them all null

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-20 : 15:06:46

LOL !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-20 : 16:04:56