Reply to: SQL Help please

[Spacecadet]

(select distinct <primary key fields> from customer_snapshot where date in (<today>, <tomorrow>) as clist
left outer join
(select * from customer_snapshot where date = <today>) as today
on clist.<pkey fields> = today.<pkey fields>
left outer join
(select * from customer_snapshot where date = <yesterday>) as yesterday
on clist.<pkey fields> = yesterday.<pkey fields>

it will hamper performance though.. there's probably a better way but with only 1 table to look at its difficult to tell

[Ruprect]

One last qn - without a full outer join available to me in sybase is there any way to do this in one query or do I just have to do 2 queries?

ta.

[Ruprect]

Thanks I'll give it a go

[Spacecadet]

use derived tables

Can't be arsed writing out the full SQL, you can do that yourself.
Heres a starter for 10 though

select <stuff>
from (select * from customer_snapshot where date = <today>) as Today
full outer join
(select * from customer_snapshot where date = <yesterday>) as Yesterday
on today.<primary key fields> = yesterday.<primary key fields>
where
<add your where clauses here>

[Ruprect]

OK Smarty pantses

how about this:

table "customer_snapshot"

fields customer_surname, customer_dob, snapshot_date, customer_email(other fields...)

for the purposes of this exercise customer_surname, customer_dob and snapshot_date are a composite PK.

What I want to know is:
1) which customers exist today that didn't exist yesterday and
2) which customers existed yesterday that don't exist today and
3) which customers have changed their email addresses since yesterday.

DB: Sybase, Number of records in table - about 2.5 million.

[gables]

I have 2 tables, e.g. customers and orders. Foreign key between is customerID. I want to know which customers have zero orders.

What SQL will achieve this?

Thanks

On a more philosophical note can you have a customer with zero orders?

[xoggoth]

Customers, orders. Dull. If I ever designed a new language I would only sell it with a condition "Not to be used in boring commercial apps"

[richard-af]

Numpties.

[ElectricChair]

A left join usually sorts it all out.

whats wrong with a right join?

[richard-af]

A left join usually sorts it all out.

Aaaagggghhhh!!!!! Sob! <clinking of gin bottle against glass>

[DimPrawn]

A left join usually sorts it all out.

[richard-af]

I am not a coder but I have come across the phenomenon that some versions of some databases will do a tablescan when you are not expecting it, so you are earning your money by knowing when that will happen.

I would vote for the subquery option because it is the one that I would write, but I can accept that in the real world that could trigger a tablescan when it shouldn't. The foreign key should be indexed in both tables otherwise the DBA should be shot.

IMO

EDIT - even if all the fields are indexed that is not always enough to stop a tablescan.

What ARE you on about? Full Table Scans, Fast Full Scans, etc. are not associated, in particular, with either Joins or Subqueries. The CBO decides HOW it will gather the data. The only way to be sure is to run it and see. Then tweaks-tests-tweaks-tests, etc. Then you know. No need to go through it all again, unless the stats become significantly out-of-step with reality, or an index is added, a table is partitioned, etc., etc.


Incidentally, a Full Table Scan can be the right thing to do.

There are books on all this, as well as bizillions of lovely web sites.

That old copy of "Oracle 7: Ye Goode Guyde To The Magicks and Mysteryes" is a bit redundant.

[Gonzo]

I am not a coder but I have come across the phenomenon that some versions of some databases will do a tablescan when you are not expecting it, so you are earning your money by knowing when that will happen.

I would vote for the subquery option because it is the one that I would write, but I can accept that in the real world that could trigger a tablescan when it shouldn't. The foreign key should be indexed in both tables otherwise the DBA should be shot.

IMO

EDIT - even if all the fields are indexed that is not always enough to stop a tablescan.

[TheFaQQer]

If only Milan were around to help you guys out, now that he's a DBA.

I think I would do it with a "where not exists" clause, but haven't looked at the plan for it.

[richard-af]

Couldn't you just ring up all the customers and ask them if they have any orders? FFS, kids of today....

Generous of spirit and magnanimous in defeat. I salute you, sir.

SQL, eh? It can be tricky, can't it?