/    Sign up×
Community /Pin to ProfileBookmark

Subqueries (or joins?)

Copy linkTweet thisAlerts:
Mar 04.2007

I’m trying to make it narrow a user’s people search down by using information in two tables. It’s supposed to return all the results, but so far I’m getting that the subquery is ambiguous

Okay here’s the code I have:

SELECT * FROM Users WHERE Gender=’Female’ AND Age>’18’ AND Age<’35’ AND AND Users.ID!=0 AND ID= ALL(SELECT ID FROM Profile WHERE Marital_Status=’Single’)

to post a comment
PHP

4 Comments(s) ↴

Copy linkTweet thisAlerts:
@michael879Mar 04.2007 — 'AND AND Users.ID'

Im not sure what the error means, but you have two ANDs in a row here, which is wrong. Try deleting one of them. Also, Im no expert on MySQL, but Im pretty sure Users.ID is invalid also. You should just use ID.
Copy linkTweet thisAlerts:
@leprakhaunsauthorMar 04.2007 — Yea the two ANDs were a typo and so is the Users.ID the code should be:


SELECT * FROM Users WHERE Gender='Female' AND Age>'18' AND Age<'35' AND ID!=0 AND ID= ALL(SELECT ID FROM Profile WHERE Marital_Status='Single')
Copy linkTweet thisAlerts:
@aussie_girlMar 04.2007 — if ID is in more than one table or you have used ID for other column names you need to prefix them with the table name

Users.ID Profile.ID
Copy linkTweet thisAlerts:
@NanscombeMar 04.2007 — Hi leprakhauns,

Try:

SELECT U.* FROM Users U, Profile P WHERE U.ID = P.ID AND U.Gender='Female' AND U.Age >'18' AND U.Age<'35' AND P.Marital_Status='Single'[/quote]

If Age was always greater than 9, then you could use:

SELECT U.* FROM Users U, Profile P WHERE U.ID = P.ID AND U.Gender='Female' AND U.Age BETWEEN '18' AND '35' AND P.Marital_Status='Single'[/quote]

Regards

Nigel
×

Success!

Help @leprakhauns spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.17,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...