36 replies to this topic

[SterAllures]

Hiya All!

First of all I want to say that I'm totally new to SQL Programming so I hope you won't laugh too hard when you see the code I wrote :p.

Well I'm curently trying to program for a Microsoft SQL 2005 Server.
And I hope I did it good but I have a little problem with the WHERE function because I assume that the rest of the code is correct(please correct me if I'm wrong:p).

I'm trying to make a view of a current existing database by the way.

Well what my WHERE Function has to do is the following. when I send someone a bill on 9-03-09 than the customer has 30 days to pay. so at 8-04-09 they have to pay. now I want to get a mail every 5 days with the bills that the people didn't pay yet.

Well here it is I hope you understand it.

CREATE VIEW Open_Fact AS


SELECT DebNr,

	ZName,

  FROM FRKRG


SELECT Price

  FROM FRXRG


SELECT OldDays

  FROM "?"

WHERE OldDays = (OldDays >= 30 AND OldDays <= 35);

So at the WHERE it must give a notification of the bills who are between the 30 and 35 days old.

Thanks In Advance!
//SterAllures

P.S. Do I also have the inner join of the multiple tables correct(tables: FRKRG, FRXRG)

[Orjan]

A view is like a server stored query to simplify retrieving information.
a view can not contain several selects, unless you union them, or join tables in a totally different way.

SELECT k.DebNr, k.ZName, x.Price, x.OldDays
FROM FRKRG k
JOIN FRXRG x on k.field1 = x.field2
WHERE OldDays BETWEEN 30 and 35

where field1 & field2 is the field names of the joining fields in the two tables.

[SterAllures]

orjan said:

A view is like a server stored query to simplify retrieving information.
a view can not contain several selects, unless you union them, or join tables in a totally different way.

SELECT k.DebNr, k.ZName, x.Price, x.OldDays

FROM FRKRG k

JOIN FRXRG x on k.field1 = x.field2

WHERE OldDays BETWEEN 30 and 35

where field1 & field2 is the field names of the joining fields in the two tables.

Wow man Thanks For This!
I see I don't understand anything of it cuase my code was totally wrong!

But I have to say. I don't fully understand some things like this:

SELECT k.DebNr, k.ZName, x.Price, x.OldDays

FROM FRKRG k

JOIN FRXRG x on k.field1 = x.field2

why do you put an k. in front of some selects? does that point to the table?
because after the FRKRG you put a k, is that like a rename for the database so it's shorter to right?
so I mean I could also do FRKRG.DebNr?

and why do you do this:

JOIN FRXRG x on k.field1 = x.field2

But thanks already for this! and thanks for your fast response man!!!

[Orjan]

SterAllures said:

Wow man Thanks For This!
I see I don't understand anything of it cuase my code was totally wrong!

But I have to say. I don't fully understand some things like this:

SELECT k.DebNr, k.ZName, x.Price, x.OldDays

FROM FRKRG k

JOIN FRXRG x on k.field1 = x.field2

why do you put an k. in front of some selects? does that point to the table?
because after the FRKRG you put a k, is that like a rename for the database so it's shorter to right?
so I mean I could also do FRKRG.DebNr?

Yes, you can shorten the table names by doing that I do on the FROM and JOIN clause. much easier than write full table names and at some points it's actually needed to do it.

SterAllures said:

and why do you do this:

JOIN FRXRG x on k.field1 = x.field2

Because the tables doesn't join on their own, you need to define that you take the FROM table, and joins it with another table, and then you specify on which specifications the join is meant to be, usually a matching ID field from each table that should be equal.

I think most RDBMS^Wiki can join two tables automatically on their primary keys, but it's not always wanted.

[SterAllures]

Wow you're fast with answering thanks!

oke I get it now a bit!
but the field1 and the field2 you just made up? so if I would say shop1 and shop2 it would be oke too? as long as you put the shorter names in front of it so they can join?

Do you maybe have some advice on a good book or a website where I can learn to make views and joins between stuff? well just a good reference on where I can learn to make a view like you just did:P

[Orjan]

yes, field1 and field2 I just made up, change to appropriate fields in your database.

one of the best tutorials I know online (except those here) is W3Schools Online Web Tutorials
has SQL tutorial as well.

views is not something you start with. A view is more or less a server saved select query. learn how to write great selects and then you can do views automatically.

I don't have any books to refer to, as I got my knowledge SQL at SQL-classes in secondary upper school and we had compendiums to learn from. I like hands on better, really, as I work more with MySQL than MS-SQL I use MySQL homepage as reference for my SQL work. Maybe something like an SQL for dummies could be a good first choice, and then go on to some more intermediate.

[SterAllures]

orjan said:

yes, field1 and field2 I just made up, change to appropriate fields in your database.

one of the best tutorials I know online (except those here) is W3Schools Online Web Tutorials
has SQL tutorial as well.

views is not something you start with. A view is more or less a server saved select query. learn how to write great selects and then you can do views automatically.

I don't have any books to refer to, as I got my knowledge SQL at SQL-classes in secondary upper school and we had compendiums to learn from. I like hands on better, really, as I work more with MySQL than MS-SQL I use MySQL homepage as reference for my SQL work. Maybe something like an SQL for dummies could be a good first choice, and then go on to some more intermediate.

Thanks for all the help you offer me!

I will definitely check out that site but I don't have time atm I'll read it tonight!

But that school must be cool!
Well I hope I'll find out how to do it and stuff and otherwise I'll bother you again:D.

Thanks Man!

[SterAllures]

well, I checked a few articles on that site you gave me and it's fantastic, really clear samples of the code and nice basic sample for every seperate kind of code.

When I have time I will read the rest but what I read now it's great!

thanks for the reference.

[SterAllures]

Hiya Again Orjan But I read some articles and looked around the internet and I came up with this. I just wanted to ask you if this is also a good way to do it or is it toally wrong, and if it is oke what is the difference between your way of join and this way?

Here it goes:

SELECT


    k.DebNr,


    k.ZName,


    x.Price,


    x.OldDays


FROM


    FRKRG k,


    FRXRG x,


WHERE


     k.field1 = x.field2

AND

     OldDays BETWEEN 30 AND 35;

Thanks In Advance!

[Orjan]

the main difference is that you can not control the type of join being made. if it gives you the wished result, it's no problem with this.

[SterAllures]

well to be honoust I like your way a whole lot better but I was just interesting if there was a difference but there is and thanks for explaining it!

another question tho!(If you don't mind)
If I'd like to join more than 2 tables how you did it what do I have to do to join another table if there are already 2 tables insert

maybe like this?

SELECT k.DebNr, k.ZName, x.Price, x.OldDays
FROM FRKRG k
JOIN FRSRG s, FRXRG x on k.field1 = x.field2
WHERE OldDays BETWEEN 30 and 35

:D(sorry If I got it totally wrong and sorry I'm like the biggest noob in this:P)

[Orjan]

[highlight="sql"]
SELECT k.DebNr, k.ZName, x.Price, x.OldDays
FROM FRKRG k
JOIN FRSRG s on k.field1 = s.field2
JOIN FRXRG x on k.field1 = x.fieldx
WHERE OldDays BETWEEN 30 and 35
[/highlight]