|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
December 31st, 2004, 09:08 PM
|
|||
|
|||
Query not working
Hello All,
I am fairly new and am having a small problem that hopefully someone can shed some light on. I have two tables with a one to many relationship, each user can have more than image. [tbluser] UserID LoginName BirthDate [tblImage] ImageID UserID ImageName ProfileImage Approved My problem is that I am not able to select a distinct set of results of all users and the ImageName if that user has an image which is approved and ProfileImage is 'yes' or '1'. Can anyone help me with writing the correct SQL for this with some basic explanation. Thanks!! Code:
SELECT tblUser.LoginName , tblUser.BirthDate , tblImage.ImageName , tblImage.ProfileImage , tblImage.Approved FROM tblUser INNER JOIN tblImage ON tblUser.UserID = tblImage.UserID WHERE (((tblImage.ProfileImage)=1) AND ((tblImage.Approved)=1)); I am using Access 2000 and using the Access Query builder.
|
|
#2
January 5th, 2005, 11:24 AM
|
|||
|
|||
|
well i am not experienced either but two things come to my mind:
a) u have used tblImage.etc etc, but u havent declared it in the from clause ie from tbleuser, tblimage b) it should be: where tableImage.profileImage = 1 AND tblImage.Approved = 1! PS> just consulted my book and it appears i am wrong on my point a. so ignore it, i m leaving it in. as you may like to try it.1 also here is another query which should give you the same results: Code:
select * from tblUser A, tblImage B
where A.userId = B.UserID AND B.Approved = " " AND B.ProfileImage IN ('Yes','1');
if that gives u duplicate replace select * with: select distinct (*) from... and finally if that doesnt work (it may not because of syntax problem) try: select distinct A.userID, A.LoginName, A.BirthDate from...... PS> i should mention that use of A and B is called alias, and it minimise the amount of writin u have to do! A and B could be anything u like/want!
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > Query not working |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
