Laphan,

select a.ACCOUNTID, a.NAME
from ACCOUNTS a,
ACCOUNTBUDGET b
where a.ACCOUNTID = b.ACCOUNTID
and b.PERIOD = 'your month'
and b.YEAR = 'your year'
and b.TURNOVER = 0

Quentin


[quoted text, click to view]

David,

you are right about the or ... is null part. Thanks.

However, I think 6.5 would have problem running the LEFT JOIN, or any JOIN
at all. Don't you think so?

Quentin

[quoted text, click to view]

Hi Guys

I don't want to keep asking for your help all the time on each individual
query, so could you please help me to break the myths on the following:

1) I have 2 tables. Once is called ACCOUNTS and the other ACCOUNTBUDGET.
ACCOUNTS includes all of the usual details for customers and suppliers, eg
the account code (as ACCOUNTID), the account name (as NAME), etc.
ACCOUNTBUDGET basically holds a transaction line for each month and it's
corresponding year to store the turnover for that month, eg one row contains
ACCOUNTID, PERIOD (ie the month), YEAR and TURNOVER.

Now a lot of the SQL 6.5 tables that I deal with are in this vein and the
usual query is that I want to list all of the ACCOUNTIDs and NAMEs that
exist in the ACCOUNTS table and then show for example what their TURNOVER is
for a applicable PERIOD and YEAR, which are all held in the ACCOUNTSBUDGET
table.

Now if I do a quick query using MS Query all I get are rows that have
related values in both the ACCOUNTS and ACCOUNTSBUDGET table when I have
specified say a certain PERIOD and YEAR.

The main point of my current reporting problem is that I want to show all
the ACCOUNTIDs and NAMEs in ACCOUNTS that have zero TURNOVER for a
particular PERIOD and YEAR.

I'm positive that I have to create a 2 step query/join, but I don't know how
to do it. What is the method? People in this NG, can rattle one up in
seconds, but I just don't see the logic. Can you help me with this query
and let me know how you manage to fathom it.

2) Are there any good web sites that explain in kiddie form how to do this
sort of thing?

I really appreciate your help on this.

Regards

Laphan

Answered in comp.databases.ms-sqlserver.
Please don't multi-post.

--
David Portas
SQL Server MVP
--

Perhaps Laphan is also looking for Accounts which don't have a corresponding
row in AccountBudget and are therefore assumed to be zero for that period.
If the result is only to include a particular year and period then it can be
done like this:

SELECT A.accountid, A.name
FROM Accounts AS A
LEFT JOIN AccountBudget AS B
ON A.accountid = B.accountid
AND B.period = <your month>
AND B.year = <your year>
WHERE B.amount=0 OR B.amount IS NULL

--
David Portas
SQL Server MVP
--

6.5 supports ANSI92 JOIN syntax. (I did actually have to look this up to be
sure)

--
David Portas
SQL Server MVP
--

Yes, but I recall some nasty bugs in 6.5, where you could get things like
"Network failure" etc when doing outer joins in 6.5 and the ANSI join
syntax. This was the first version that supported "ANSI joins". Perhaps the
majority of bugs were fixed in later service packs, but to some extent, I'm
happy that most programmers were still using the old join syntax in the 6.5
days. :-)
Or perhaps it was only a few special circumstances that I happened to run
into and I based my judgment on those...
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver


[quoted text, click to view]

Dear All

Many thanks for your valued feedback.

To try and explain myself, I have listed my current query as follows:

SELECT ACCOUNTS.ACCOUNTID, ACCOUNTS.NAME, CONTACTDETAILS.TELEPHONE,
ACCOUNTSBUDGET.TURNOVER

(I think the above is standard faire, but please let me know if it doesn't
make sense)

FROM ACCOUNTS, ACCOUNTSBUDGET, CONTACTDETAILS, PEOPLE

(ditto)

WHERE PEOPLE.PEOPLEID = ACCOUNTS.PEOPLEID AND CONTACTDETAILS.PEOPLEID =
PEOPLE.PEOPLEID AND ACCOUNTSBUDGET.ACCOUNTID = ACCOUNTS.ACCOUNTID AND
((ACCOUNTS.LEDGER=1) AND (ACCOUNTSBUDGET.YEAR=2002) AND
(ACCOUNTSBUDGET.PERIOD=6) AND (CONTACTDETAILS.SALESACCOUNTSCONTACT=1))

The 'ACCOUNTS.LEDGER=1' part simply filters customers from suppliers in the
ACCOUNTS table, ie customers = 1 and suppliers = 2.

The 'ACCOUNTSBUDGET.YEAR=2002' part is a Year filter, which I will make
variable later on so that I can let the user pick a specific year. I just
left it to 2002 to check that the query worked.

The 'ACCOUNTSBUDGET.PERIOD=6' part is a Month filter, which I will make
variable later on so that I can let the user pick a specific Month. I just
left it to 6 (which is June) to check that the query worked.

The 'CONTACTDETAILS.SALESACCOUNTSCONTACT=1' part simply filters account
contacts from other types in the CONTACTDETAILS table, ie
SALESACCOUNTSCONTACT=1 is a sales accounts contact and
SALESACCOUNTSCONTACT=0 isn't.

I want to list all of the ACCOUNTIDs and NAMEs in the ACCOUNTS table and
TELEPHONE in the CONTACTDETAILS where they either have a TURNOVER of zero in
the ACCOUNTSBUDGET table for the PERIOD and YEAR that the user specifies or
they don't have a value at all in the ACCOUNTSBUDGET table for the PERIOD
and YEAR that the user specifies, as this record 'no show' denotes that they
haven't any turnover either.

I'm sure its a join that I'm missing, as I'm trying to do 2 sorts of queries
in one - right, but I just can't get my head round how this is done.

Any ideas?

Rgds

Robbie

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:%23vzJUQV7DHA.1592@TK2MSFTNGP10.phx.gbl...
Yes, but I recall some nasty bugs in 6.5, where you could get things like
"Network failure" etc when doing outer joins in 6.5 and the ANSI join
syntax. This was the first version that supported "ANSI joins". Perhaps the
majority of bugs were fixed in later service packs, but to some extent, I'm
happy that most programmers were still using the old join syntax in the 6.5
days. :-)
Or perhaps it was only a few special circumstances that I happened to run
into and I based my judgment on those...
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver


[quoted text, click to view]

The best way to get help with your problem is to post DDL, some sample data
and show your required result.

http://www.aspfaq.com/5006

--
David Portas
SQL Server MVP
--

Many thanks for bearing with me on this David.

I will get to grips with the DDL, inserts, etc, but your posting of 6-2-04
22:35 is nearly exactly what I want.

All I need to do now is get the TELEPHONE field out of my CONTACTDETAILS
table which is linked to my ACCOUNTS table by a mid-association of my PEOPLE
table, eg

PEOPLEID in ACCOUNTS to PEOPLEID in PEOPLE
CONTACTID in PEOPLE to CONTACTID in CONTACTDETAILS
TELEPHONE is taken out of CONTACTDETAILS
with an additional WHERE for the SALESACCOUNTID = 1

If possible, could you please explain where I would stick these in, in the
script that you have already sent me.

Rgds

Laphan



[quoted text, click to view]
The best way to get help with your problem is to post DDL, some sample data
and show your required result.

http://www.aspfaq.com/5006

--
David Portas
SQL Server MVP
--

For the best explanation open Query Analyser, paste your query, set ShowPlan
on and execute. You will see the plan that SQL Server has determined for
getting your data. Sometimes it is helpful to view this to ensure that
table scans are not being performed.

The showplan will have various steps which are documented in Books Online
and present the clearest explanation of what is happening in your query.

Lloyd Sheen

[quoted text, click to view]

Dear David

Sorry for my past cry for help before trying, but I think I've done it!!!

I get the result I want by creating the below:

SELECT A.accountid, A.name, C.telephone
FROM Accounts AS A
JOIN People as P
ON P.peopleid = A.peopleid
JOIN contactdetails as C
ON C.peopleid = P.peopleid
LEFT JOIN AccountsBudget AS B
ON A.accountid = B.accountid
AND B.period = 5
AND B.year = 2003
WHERE (A.ledger=1) AND (B.turnover=0 OR B.turnover IS NULL)

I know I shouldn't play at these things, but could you please explain to me
how the above works and more importantly if I am doing something terribly
wrong.

Many thanks for your time.

Rgds

Laphan




[quoted text, click to view]
The best way to get help with your problem is to post DDL, some sample data
and show your required result.

http://www.aspfaq.com/5006

--
David Portas
SQL Server MVP
--

Would it really make you more confident if I said it would work without
having tested it against your data? :-)

Joins are straightforward if you've declared all the foreign key
constraints. If you haven't declared the FKs (peoplid and accountid) then
check that the joins aren't dropping rows that you want to include.

--
David Portas
SQL Server MVP
--

I'm a nuisance aren't I :0)

Thanks for your patience David (and your feedback Lloyd)

The thing that I want to get my head round is these joins.

The info that I have read on them doesn't explain it in a practical manner
and this is where I getting lost.

Bring back Wrox that's all I can say.

I going to go away and learn a bit more before I trouble you again ....
promise .... :0)

Rgds

Laphan

[quoted text, click to view]
Would it really make you more confident if I said it would work without
having tested it against your data? :-)

Joins are straightforward if you've declared all the foreign key
constraints. If you haven't declared the FKs (peoplid and accountid) then
check that the joins aren't dropping rows that you want to include.

--
David Portas
SQL Server MVP
--

What part of the join is giving you problems. This is "new" syntax to
specify fields to join queries on. Basically there are two type of joins,
INNER and OUTER.

The INNER join returns only the rows for which there are equal values in
both tables for a particular value.

OUTER joins are used to return rows where there is no matching data in the
joined table.

Hope this helps.

Lloyd

[quoted text, click to view]

Hi Lloyd

Thanks for coming back to me.

Do I take it that INNER joins are therefore the default setting when you
join 2 tables together, eg

SELECT A.accountid, A.name, B.telephone
FROM Accounts AS A, Contacts AS B
WHERE A.accountid = B.accountid

IN other words I don''t have to put INNER in the where??

Where does David's LEFT JOIN come in then is this an INNER, an OUTER or
something completely different?

Why in the shoddy script that I created, did I have to JOIN the other tables
that I needed to David's original?

I don't understand how David managed to make his original in that he knew
that the LEFT JOIN plus the ON would do the trick, it's this part that
escapes me.

Please accept my apologies for my blonde answers (I am mucky blonde after
all!!), but I just think that if I can get my head round this bit, I'm at
the top of the hill with it. So to speak.

Rgds

Laphan

[quoted text, click to view]
What part of the join is giving you problems. This is "new" syntax to
specify fields to join queries on. Basically there are two type of joins,
INNER and OUTER.

The INNER join returns only the rows for which there are equal values in
both tables for a particular value.

OUTER joins are used to return rows where there is no matching data in the
joined table.

Hope this helps.

Lloyd

[quoted text, click to view]

[quoted text, click to view]


That's right. If there are no JOIN clauses at all then a logical expression
in a WHERE clause has the same effect as using the same expression for an
INNER JOIN.


[quoted text, click to view]

It's an OUTER join. LEFT JOIN is just a shortened form of LEFT OUTER JOIN -
the OUTER keyword is optional.

To expand on what Lloyd has said, SQLServer supports three of the join types
defined by the ANSI/ISO standard syntax:

INNER JOIN
OUTER JOIN (LEFT or RIGHT)
CROSS JOIN


[quoted text, click to view]

Shoddy?

Lookup the topic "Using Cross Joins" in Books Online. If two tables included
in a query are not explicitly joined, either in a WHERE or INNER/OUTER JOIN
clause, then every row in one table will be joined to every row in the
other - not usually what is required, although sometimes it's useful, for
example see my original reply to your post in comp.databases.ms-sqlserver.


[quoted text, click to view]

The point of the LEFT JOIN is that rows from Accounts will be included even
if they don't exist in AccountBudget. In that case the Amount column will be
populated with NULLs which is the case that you are interested in (WHERE ...
B.amount IS NULL).


BOL has some more examples.
http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_09_610z.asp

--
David Portas
SQL Server MVP
--