Solved

search an sql database

Posted on 2012-03-19
3
238 Views
Last Modified: 2012-03-19
Table 'people'
columns 'username','customfield','customvalue'
Table 'custom-fields'
columns 'fieldID','fieldName'

Data in 'people' table
joe, 2,small
joe,3,black
joe,4,deaf
pat,2,small
pat,3,brown
pat,4,blind
lisa,2,big
lisa,4,blind
alex,2,medium
alex,3,white
alex,4,deaf

Data in 'customfields' table
1,gender
2,size
3,color
4,handicap

I want to get a subset that finds all people who have the handicap of being 'blind' and returns all data about them

ie
pat,2,small
pat,3,brown
pat,4,blind
lisa,2,big
lisa,4,blind

select * from people p left join customfields on p.customfield = c.fieldID where c.fieldID=4 and p.customvalue="blind"

This finds
pat,4,blind
lisa,4,blind

how do I get the more complete list
0
Comment
Question by:joomla
3 Comments
 
LVL 24

Accepted Solution

by:
johanntagle earned 250 total points
ID: 37736639
Try:

select pp.* from people join people p on pp.username=p.username left join customfields on p.customfield = c.fieldID where c.fieldID=4 and p.customvalue="blind"
0
 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37736660
select * from people a inner join customfields b on a.customfield=b.fieldid where b.fieldid=4 and p.customvalue='blind'

Open in new window

0
 

Author Closing Comment

by:joomla
ID: 37736699
thank you
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now