Search question - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Search question

Author

Topic

neutcomp
Posting Yak Master

111 Posts

Posted - 2004-03-05 : 05:46:20

Hello again,

I have 4 tabels:
tblusers, tblnaw, tblpresentatie, tblcompositie

tblusers
idUser
nickname
status

tblnaw
idNaw
idUser
gender

tblpresentatie
idPresentatie
idUser
foto
snapshot
video

tblcompositie
idCompositie
idUser
image

No I want to search for users how are male or female (tblnaw.gender).
And I want to give back everything the have like a foto or snapshot or composition. Oke not a big problem there.

But people can have
1) foto, video, snapshot, composition (all)
2) foto (only one)
3) notting

Query:

SELECT a.idUser, a.status, c.gender, a.nickname, d.idCompositie, e.foto, e.snapshot, e.video
FROM tblusers as a, tblnaw AS c, tblcompositie AS d, tblpresentatie AS e 

INNER JOIN tblpresentatie
 ON (a.idUser = tblpresentatie.idUser)
INNER JOIN tblcompositie
 ON (a.idUser = tblcompositie.idUser)
 
AND c.gender = "male"
GROUP BY idUser

But know I get only back the people how have records in all tabels.
How can I fix this? Hope you understand my problem. If not please feal free to ask more info!

thanxx
Bjorn

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-03-05 : 06:06:09

USE LEFT JOIN instead of INNER JOIN. That will return all users from tblUsers and any associated records from the joined tables.

Raymond

neutcomp
Posting Yak Master

111 Posts

Posted - 2004-03-05 : 06:25:21

Oke thanxx I will try that!

Love this forum!! Always fast respons!!
Keep up the good work.

Cya
Bjorn

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-05 : 08:24:24

Just a comment: I think having an table like you have with male/female in it is quite an overkill. I usually have Sex as an attribute of User/Person with varchar(2) as datatype and M as male and F as female...

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-03-05 : 09:01:07

Varchar(2)?

....M is 1 char, F is 1 char...
what's the other char for?
na, tx, vg?

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-03-05 : 09:04:55

UK = un known

LOL

Jim
Users <> Logic

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-03-05 : 10:53:59

And dont forget another option: "I would rather not say"

neutcomp
Posting Yak Master

111 Posts

Posted - 2004-03-09 : 03:46:49

Oke did not work as I would like:

have a other simpel table:

SELECT a.name, b.login
FROM tbllogin AS a, tblprivate AS b
LEFT JOIN tblprivate
on( a.idUser = b.idUser)
WHERE a.idUser = 1

idUser is known in tbllogin but not in tblprivate
So I want as return

name login
Bjorn NULL

But now I get
NULL NULL

Hope I am clear and you can help me out!

thanxx
Bjorn

xpandre
Posting Yak Master

212 Posts

Posted - 2004-03-09 : 04:20:51

select a.name,b.login from
tbllogin a left outer join tblprivate b
on a.id=b.id

neutcomp
Posting Yak Master

111 Posts

Posted - 2004-03-09 : 06:10:01

Oke thanxx,

that works but I also have b.idHairColor that correspond with tblhair (idHairColor, color)

SELECT a.name, b.login, c.color
FROM tbllogin AS a LEFT OUTER JOIN tblprivate AS b USING (id), tblhair AS c
WHERE b.idHairColor = c.idHairColor

Or

SELECT a.name, b.login, c.color
FROM tbllogin AS a LEFT OUTER JOIN tblprivate AS b USING (id), tblprivate AS bb LEFT OUTER JOIN tblhair AS c USING (idHairColor)

Does not give me the right result. The First one is giving me notting because WHERE b.idHairColor = c.idHairColor is not true!
And the second one is giving me a result but takes the haircolor from someother person!

Thanxx
Bjorn

xpandre
Posting Yak Master

212 Posts

Posted - 2004-03-09 : 06:27:22

but wats the relation between tblhair,tbllogin and tblprivate?

neutcomp
Posting Yak Master

111 Posts

Posted - 2004-03-09 : 06:30:05

xpandre
Posting Yak Master

212 Posts

Posted - 2004-03-09 : 06:49:58

try this out

select a.name,b.name,
(select c.color from tblhair c where c.idhaircolor=b.idhaircolor) color from
tbllogin a left outer join tblprivate b
on a.id=b.id

neutcomp
Posting Yak Master

111 Posts

Posted - 2004-03-09 : 07:29:49

I get an error! Maby this is because I use mysql?

select a.name, b.login,
(select c.color from tblhair c where c.idHairColor = b.idHairColor) color
from tbllogin a left outer join tblprivate b USING(id)

Hope you can help me out!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources