Solved

query for two tables

Posted on 2004-08-06
7
198 Views
Last Modified: 2010-04-06
i have two table, one for project name and the type of project like this

Project

ID     project_name
         
1      project one
2      project two
3      project three

Type

typeid          ID     type_project   minprice         maxprice
1        1        residential     2,717,000.00      4,197,000.00
2        1      commercial     408,000.00        983,000.00
3        1      industrial       399,600.00       1,000,000.00  
4        2      commercial     691,000.00         2,273,500.00
5        2      residential     263,000.00        2,267,000.00
6        3      commercial       194,700.00        341,500.00

my query is
Select * from project,type where type.minprice >= 500000 and type.maxprice <= 1000000 and project.ID = type.ID order by project.project_name asc"

it should display these:

2     1      commercial     408,000.00     983,000.00 = because 983,000.00 is less than 1000000

3     1      industrial    399,600.00          1,000,000.00  = because 1,000,000.00 is equal to 1000000

4     2      commercial     691,000.00         2,273,500.00 = because 691,000.00 is less than 1000000


what should be my query?

pls. help
       
0
Comment
Question by:amerliwayway
  • 2
  • 2
7 Comments
 

Author Comment

by:amerliwayway
ID: 11734299
the DB i am using is MS ACCESS
0
 
LVL 35

Expert Comment

by:YZlat
ID: 11734783
Select Type.typeid,Project.ID,Type.type_project,Type.minprice,Type.maxprice from project,type where type.minprice >= 500000 OR type.maxprice <= 1000000 and project.ID = type.ID order by project.project_name asc"
0
 
LVL 35

Expert Comment

by:YZlat
ID: 11734789
opps, replace OR with AND
0
 
LVL 7

Accepted Solution

by:
Tanglin05 earned 75 total points
ID: 11736631
SELECT Type.typeID, Project.ID, Type.type_project, Type.minprice, Type.maxprice

FROM Type INNER JOIN Project ON Type.ID = Project.ID

WHERE (Type.minprice >= 500000 AND Type.maxprice <= 1000000)

ORDER BY Project.project_name;
0
 
LVL 7

Expert Comment

by:Tanglin05
ID: 11736659
It is always better for security and performance not to use the "*" wildcard in database select statements. Otherwise, the INNER JOIN is a more graceful way of connecting your two tables- where all records with matching values will be returned.

To learn more about forming SQL statements, this web site is VERY helpful and easy to use:

http://www.w3schools.com/sql/default.asp
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you work with shopping cart / ecommerce relates web sites, you need to pass the certain form post details to the payment gateway process page with required details for the products items you give to order. Also you may need to track the ordered…
Preface This is the third article about the EE Collaborative Login Project. A Better Website Login System (http://www.experts-exchange.com/A_2902.html) introduces the Login System and shows how to implement a login page. The EE Collaborative Logi…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…

813 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

12 Experts available now in Live!

Get 1:1 Help Now