Help with SQL-code - 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

Database Design and Application Architecture

Help with SQL-code

Author

Topic

Deeo
Starting Member

5 Posts

Posted - 2007-10-25 : 07:34:10

Hello everyone!

I need some help with my code. It says that (the blood marked) is wrong.
I want to enter a kund number and it will show his first and last name and what product he/she has ordered.


SELECT Kunder.Kundnr, Kunder.Fnamn, Kunder.Enamn, Lager.Artnr
FROM Kunder
INNER JOIN Lager
ON Lager.Artnr=Order.Artnr AND  Kunder.Kundnr=Order.Kundnr
WHERE Kunder.Kundnr= [Ange Kundnr];

I appreciate all the help I get.

Deeo

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-25 : 07:42:54

quote:
Originally posted by Deeo

Hello everyone!

I need some help with my code. It says that (the blood marked) is wrong.
I want to enter a kund number and it will show his first and last name and what product he/she has ordered.

SELECT Kunder.Kundnr, Kunder.Fnamn, Kunder.Enamn, Lager.Artnr
FROM Kunder
INNER JOIN Lager
ON Lager.Artnr=Kunder.Artnr AND Kunder.Kundnr=Lager.Kundnr
WHERE Kunder.Kundnr= [Ange Kundnr];

I appreciate all the help I get.

Deeo

You don't have ORDER table included in join so you can't refer it in WHERE clause.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-25 : 07:45:13

And this is a MS Access query.

Moderator, please move to access forum.

E 12°55'05.25"
N 56°04'39.16"

Deeo
Starting Member

5 Posts

Posted - 2007-10-25 : 08:14:50

Okey, thanks! It worked ^_^

But no it doesn't show what order the customers order so I did like this:


SELECT Kunder.Kundnr, Kunder.Fnamn, Kunder.Enamn, Lager.Artnr
FROM Kunder
INNER JOIN Lager
ON Lager.Artnr=Kunder.Artnr AND Kunder.Kundnr=Lager.Kundnr
INNER JOIN Order
ON Order.Kundr=Kunder.Kundnr AND Order.Artnr=Lager.Artnr
WHERE Kunder.Kundnr= [Ange Kundnr];

But now I get an other error. Any id¨¦?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-25 : 08:19:43

And the new error is..?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-25 : 08:21:04

Try LEFT JOIN.

SELECT		Kunder.Kundnr,
		Kunder.Fnamn,
		Kunder.Enamn,
		Lager.Artnr
FROM		Kunder
LEFT JOIN	Lager ON Lager.Kundnr = Kunder.Kundnr AND Lager.Artnr = Kunder.Artnr
LEFT JOIN	[Order] ON Order.Kundr = Kunder.Kundnr AND Order.Artnr = Lager.Artnr
WHERE		Kunder.Kundnr = [Ange Kundnr];

E 12°55'05.25"
N 56°04'39.16"

Deeo
Starting Member

5 Posts

Posted - 2007-10-25 : 08:28:55

quote:
Originally posted by Peso

And the new error is..?

E 12�05.25"
N 56�39.16"

[url]http://img80.imageshack.us/my.php?image=26677346gc7.jpg[/url]

Deeo
Starting Member

5 Posts

Posted - 2007-10-25 : 08:35:45

quote:

Originally posted by Peso

Try LEFT JOIN.

SELECT		Kunder.Kundnr,
		Kunder.Fnamn,
		Kunder.Enamn,
		Lager.Artnr
FROM		Kunder
LEFT JOIN	Lager ON Lager.Kundnr = Kunder.Kundnr AND Lager.Artnr = Kunder.Artnr
LEFT JOIN	[Order] ON Order.Kundr = Kunder.Kundnr AND Order.Artnr = Lager.Artnr
WHERE		Kunder.Kundnr = [Ange Kundnr];

E 12�05.25"
N 56�39.16"

LEFT JOIN didn't work =/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-25 : 08:40:27

This maybe?

SELECT		Kunder.Kundnr,
		Kunder.Fnamn,
		Kunder.Enamn,
		Lager.Artnr
FROM		Kunder
INNER JOIN	[Order] ON Order.Kundr = Kunder.Kundnr
INNER JOIN	Lager ON Lager.Artnr = Order.Artnr
WHERE		Kunder.Kundnr = [Ange Kundnr];

It's almost impossible to tell since we do not know anything about your table layouts.

E 12°55'05.25"
N 56°04'39.16"

Deeo
Starting Member

5 Posts

Posted - 2007-10-25 : 08:55:01

[url]http://img292.imageshack.us/my.php?image=53580026ni4.jpg[/url]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-25 : 10:02:53

[code]SELECT Kunder.Kundnr,
Kunder.Fnamn,
Kunder.Enamn,
Hjalptabell.Artnr
FROM Kunder
INNER JOIN [Order] ON Order.Kundr = Kunder.Kundnr
INNER JOIN Hjalptabell ON Hjalptabell.Ordernr = [Order].Ordernr
WHERE Kunder.Kundnr = [Ange Kundnr];[/code]

E 12°55'05.25"
N 56°04'39.16"

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-10-25 : 14:13:21

Deeo,

Stop posting links to images on other sites and provide the information we need to help, please.

dofree
Starting Member

3 Posts

Posted - 2007-11-06 : 13:41:56

I think the error is because you use the word: ORDER.
I often get error in diffrent kind of DB's, like NexusDB and MS Access.
Try to put " arund or []. Usely helps.

http://www.dofree.se Program för; Fakturering / Bokföring / Inkasso m.m. även GRATIS!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources