Help with MSSQL select query

Question

Peric

15 Years Ago

So, i'm working on VB.NET application but I'm having problem with one MSSQL query.

SELECT ISNULL(Sezona.NogSezona, '') FROM Trener LEFT JOIN Licenca ON Trener.BrTrenerskeIskaznice = Licenca.BrTrenerskeIskaznice LEFT JOIN Sezona ON Sezona.IDSezona = Licenca.IDSezona AND Licenca.IDSezona=3

This is the code I've been using 'til now, but it's not working as I wanted.
The thing is...I need to check if "Trener"(Coach, which has unique identification) have "Licenca"(license) with "IDSezone=3"(IDSeason). If Trener("Coach") have a license with "IDSezone=3"(IDSeason) print "NogSezona" string...if it doesn't have, print ''.
This code above, I thought it works fine, but if you have two license/records for one coach, one with "IDSezone=3" and one with "IDSezone=4", query will also return '' for that record with "IDSezone=4"...but I need a query that will look at this record like it doesn't exist...I need query that will always return let's say 429 rows(if 429 coaches are in database), and that will not look if one coach is having more license...
I hope you understand what I need, so i will appreciate if someone can help me.

* I'm from Croatia, so that's why i've been translating some words.

mssql

2 Contributors
7 Replies
235 Views
6 Hours Discussion Span
Latest Post 15 Years Ago Latest Post by Peric

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

Please post your table sturcture of both tables. also specify the primary key and foriegin key

urtrivedi 276 Nearly a Posting Virtuoso

15 Years Ago

Following query will show all trainers, and their Sezona.NogSezona if they have idsezona =3 else it will show null

SELECT Trener.BrTrenerskeIskaznice , ISNULL(Sezona.NogSezona, '') 
FROM Trener LEFT JOIN Licenca ON Trener.BrTrenerskeIskaznice = Licenca.BrTrenerskeIskaznice  [b]and Licenca.IDSezona=3 [/b]
LEFT JOIN Sezona ON Sezona.IDSezona = Licenca.IDSezona

Edited 15 Years Ago by peter_budo because: Correcting closing tag

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

urtrivedi 276 Nearly a Posting Virtuoso · Answer 1 · 2009-08-29T14:53:20+00:00

TRY FOLLOWING QUERY

SELECT ISNULL(Sezona.NogSezona, '') 
FROM Trener LEFT JOIN Licenca ON Trener.BrTrenerskeIskaznice = Licenca.BrTrenerskeIskaznice 
LEFT JOIN Sezona ON Sezona.IDSezona = Licenca.IDSezona 
[B]WHERE[/B] Licenca.IDSezona=3

Peric · Answer 2 · 2009-08-29T14:56:07+00:00

ok, hope this helps...

"Licenca" (License) - BrTrenerskeIskaznice, IDNogSrediste, IDSezona, IDKlub, IDKategorija, IDRang are Foreign keys

BrLicence	int	no	4	10   	0    	yes	(n/a)	(n/a)	NULL
PromjenaKluba	smalldatetime	no	4	     	     	yes	(n/a)	(n/a)	NULL
Status	bit	no	1	     	     	yes	(n/a)	(n/a)	NULL
BrTrenerskeIskaznice	int	no	4	10   	0    	yes	(n/a)	(n/a)	NULL
IDNogSrediste	int	no	4	10   	0    	yes	(n/a)	(n/a)	NULL
IDSezona	int	no	4	10   	0    	yes	(n/a)	(n/a)	NULL
IDKlub	int	no	4	10   	0    	yes	(n/a)	(n/a)	NULL
IDKategorija	int	no	4	10   	0    	yes	(n/a)	(n/a)	NULL
IDRang	int	no	4	10   	0    	yes	(n/a)	(n/a)	NULL

"Trener" (coach) - where "BrTrenerskeIskaznice" is primary key, IDZvanjeHR and IDZvanjeEU are foreign keys

BrTrenerskeIskaznice	int	no	4	10   	0    	no	(n/a)	(n/a)	NULL
Prezime	varchar	no	40	     	     	yes	no	yes	Croatian_CI_AS
Ime	varchar	no	40	     	     	yes	no	yes	Croatian_CI_AS
JMBG	char	no	13	     	     	yes	no	yes	Croatian_CI_AS
DatumRodjenja	smalldatetime	no	4	     	     	yes	(n/a)	(n/a)	NULL
MjestoRodjenja	varchar	no	50	     	     	yes	no	yes	Croatian_CI_AS
Adresa	varchar	no	50	     	     	yes	no	yes	Croatian_CI_AS
PostanskiBroj	char	no	5	     	     	yes	no	yes	Croatian_CI_AS
StrucnaSprema	char	no	7	     	     	yes	no	yes	Croatian_CI_AS
Zanimanje	varchar	no	40	     	     	yes	no	yes	Croatian_CI_AS
Telefon1	varchar	no	30	     	     	yes	no	yes	Croatian_CI_AS
Telefon2	varchar	no	30	     	     	yes	no	yes	Croatian_CI_AS
Gsm1	varchar	no	30	     	     	yes	no	yes	Croatian_CI_AS
Gsm2	varchar	no	30	     	     	yes	no	yes	Croatian_CI_AS
Fax	varchar	no	30	     	     	yes	no	yes	Croatian_CI_AS
Email	varchar	no	50	     	     	yes	no	yes	Croatian_CI_AS
Status	bit	no	1	     	     	yes	(n/a)	(n/a)	NULL
Napomene	varchar	no	160	     	     	yes	no	yes	Croatian_CI_AS
StrucnoUsavr	varchar	no	100	     	     	yes	no	yes	Croatian_CI_AS
Tecaj	varchar	no	100	     	     	yes	no	yes	Croatian_CI_AS
Slika	varchar	no	150	     	     	yes	no	yes	Croatian_CI_AS
Domovnica	varchar	no	150	     	     	yes	no	yes	Croatian_CI_AS
Diploma	varchar	no	150	     	     	yes	no	yes	Croatian_CI_AS
PolozioDatum	smalldatetime	no	4	     	     	yes	(n/a)	(n/a)	NULL
IDZvanjeEU	int	no	4	10   	0    	yes	(n/a)	(n/a)	NULL
IDZvanjeHR	int	no	4	10   	0    	yes	(n/a)	(n/a)	NULL

"Sezona" (season) - where "IDSezona" is primary key

IDSezona	int	no	4	10   	0    	no	(n/a)	(n/a)	NULL
NogSezona	varchar	no	15	     	     	yes	no	yes	Croatian_CI_AS

Peric · Answer 3 · 2009-08-29T15:09:47+00:00

TRY FOLLOWING QUERY

SELECT ISNULL(Sezona.NogSezona, '') 
FROM Trener LEFT JOIN Licenca ON Trener.BrTrenerskeIskaznice = Licenca.BrTrenerskeIskaznice 
LEFT JOIN Sezona ON Sezona.IDSezona = Licenca.IDSezona 
[B]WHERE[/B] Licenca.IDSezona=3

ok, i've tried this one before and again, is not what i need... this one selects only records where "IDSezona=3" and does not put '' where record for Coach doesn't exist...it only select 175 records I have with IDSezona=3, and i 428(number of coaches) records...
tnx anyway

Peric · Answer 4 · 2009-08-29T15:12:05+00:00

ok, hope this helps...

"Licenca" (License) - BrTrenerskeIskaznice, IDNogSrediste, IDSezona, IDKlub, IDKategorija, IDRang are Foreign keys

BrLicence	int	no	4	10   	0    	yes	(n/a)	(n/a)	NULL
PromjenaKluba	smalldatetime	no	4	     	     	yes	(n/a)	(n/a)	NULL
Status	bit	no	1	     	     	yes	(n/a)	(n/a)	NULL
BrTrenerskeIskaznice	int	no	4	10   	0    	yes	(n/a)	(n/a)	NULL
IDNogSrediste	int	no	4	10   	0    	yes	(n/a)	(n/a)	NULL
IDSezona	int	no	4	10   	0    	yes	(n/a)	(n/a)	NULL
IDKlub	int	no	4	10   	0    	yes	(n/a)	(n/a)	NULL
IDKategorija	int	no	4	10   	0    	yes	(n/a)	(n/a)	NULL
IDRang	int	no	4	10   	0    	yes	(n/a)	(n/a)	NULL

"Trener" (coach) - where "BrTrenerskeIskaznice" is primary key, IDZvanjeHR and IDZvanjeEU are foreign keys

BrTrenerskeIskaznice	int	no	4	10   	0    	no	(n/a)	(n/a)	NULL
Prezime	varchar	no	40	     	     	yes	no	yes	Croatian_CI_AS
Ime	varchar	no	40	     	     	yes	no	yes	Croatian_CI_AS
JMBG	char	no	13	     	     	yes	no	yes	Croatian_CI_AS
DatumRodjenja	smalldatetime	no	4	     	     	yes	(n/a)	(n/a)	NULL
MjestoRodjenja	varchar	no	50	     	     	yes	no	yes	Croatian_CI_AS
Adresa	varchar	no	50	     	     	yes	no	yes	Croatian_CI_AS
PostanskiBroj	char	no	5	     	     	yes	no	yes	Croatian_CI_AS
StrucnaSprema	char	no	7	     	     	yes	no	yes	Croatian_CI_AS
Zanimanje	varchar	no	40	     	     	yes	no	yes	Croatian_CI_AS
Telefon1	varchar	no	30	     	     	yes	no	yes	Croatian_CI_AS
Telefon2	varchar	no	30	     	     	yes	no	yes	Croatian_CI_AS
Gsm1	varchar	no	30	     	     	yes	no	yes	Croatian_CI_AS
Gsm2	varchar	no	30	     	     	yes	no	yes	Croatian_CI_AS
Fax	varchar	no	30	     	     	yes	no	yes	Croatian_CI_AS
Email	varchar	no	50	     	     	yes	no	yes	Croatian_CI_AS
Status	bit	no	1	     	     	yes	(n/a)	(n/a)	NULL
Napomene	varchar	no	160	     	     	yes	no	yes	Croatian_CI_AS
StrucnoUsavr	varchar	no	100	     	     	yes	no	yes	Croatian_CI_AS
Tecaj	varchar	no	100	     	     	yes	no	yes	Croatian_CI_AS
Slika	varchar	no	150	     	     	yes	no	yes	Croatian_CI_AS
Domovnica	varchar	no	150	     	     	yes	no	yes	Croatian_CI_AS
Diploma	varchar	no	150	     	     	yes	no	yes	Croatian_CI_AS
PolozioDatum	smalldatetime	no	4	     	     	yes	(n/a)	(n/a)	NULL
IDZvanjeEU	int	no	4	10   	0    	yes	(n/a)	(n/a)	NULL
IDZvanjeHR	int	no	4	10   	0    	yes	(n/a)	(n/a)	NULL

"Sezona" (season) - where "IDSezona" is primary key

IDSezona	int	no	4	10   	0    	no	(n/a)	(n/a)	NULL
NogSezona	varchar	no	15	     	     	yes	no	yes	Croatian_CI_AS

I forget to write Foreign keys, so I edited this post...

Peric · Answer 5 · 2009-08-29T15:14:44+00:00

Following query will show all trainers, and their Sezona.NogSezona if they have idsezona =3 else it will show null
SELECT Trener.BrTrenerskeIskaznice , ISNULL(Sezona.NogSezona, '') 
FROM Trener LEFT JOIN Licenca ON Trener.BrTrenerskeIskaznice = Licenca.BrTrenerskeIskaznice  [b]and Licenca.IDSezona=3 [/b]
LEFT JOIN Sezona ON Sezona.IDSezona = Licenca.IDSezona

Ok...that's it :)
Thank you very much, I really appreciate it :)