• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 453
  • Last Modified:

Complex SQL or Multiple Queries with PHP/MYSQL

Hey everyone. I'm no expert when it comes to SQL and I'm creating my most complex site to date. I have a very complex query I need to create and I'm not sure what the best method it so reach my goal. I'm going to try and explain my db structure simply without confusing anyone (or myself)(they are a lot bigger than I'm showing here).

Table A
id | proptype | nid | price

Table B
unl_uap_id | unl_nh_id

Table C
uap_id | uap_uid | uap_proptype | uap_price_min | uap_price_max

Table D
u_id | u_mobile

Relationships between the tables:
A.proptype <> C.uap_proptype
A.nid <> B.unl_nh_id
B.unl_uap_id <> C.uap_id
C.uap_uid <> D.u_id

What I am trying to do is I am starting at Table A and I need to u_mobile in Table D. (There can be many u_mobile returned)
Comparing A.nid = B.unl_nh_id would return a list of unl_uap_id (many)
Comparing B.unl_uap_id = C.uap_id returns 1 row
Also, I need to ensure that A.proptype = C.uap_proptype AND A.price >= C.uap_price_min AND A.price <= C.uap_price_max

from the list of uap_id I need to find u_mobile from C.uap_id = D.u_id

I'm not sure if I explained that well, but I hope someone can help me out. I started doing nested subqueries, but got lost in my own code. Any help is really appreciated.
0
fishtank22
Asked:
fishtank22
  • 3
  • 3
1 Solution
 
Arthur_WoodCommented:
try this:

Select A.*, D.u_mobile
   FROM (((A INNER JOIN B on A.nid = B.unl_nh_id ) INNER JOIN C on B.unl_uap_id = C.uap_id) INNER JOIN D on C.uap_id = D.u_id)
   WHERE A.proptype = C.uap_proptype AND A.price >= C.uap_price_min AND A.price <= C.uap_price_max

AW
0
 
LowfatspreadCommented:
please tell us which version of mysql you're working with ...
0
 
fishtank22Author Commented:
Arthur,

Wow...Thanks a lot for the help. I really appreciate it. I can't make heads or tails of this yet, but I will after I break it down a bit....

I'm trying to implement this as you have suggested. If I am starting with knowing Table A's id value of say 55. where would I put that in the query. Also some of my table names are long and will complicate this a good deal. Where can I put in the shortcut names TABLE A as A etc to clean this up. Or should I not do that.

I am using mySQL version 4.1.16. Hope that helps you guys out.

Really... thanks a LOT for the help.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Arthur_WoodCommented:
Select A.*, D.u_mobile
   FROM (((MyFirstTableWhichHasALongName as A INNER JOIN MySecondTableWhichHasALongName as B on A.nid = B.unl_nh_id ) INNER JOIN C on B.unl_uap_id = C.uap_id) INNER JOIN D on C.uap_id = D.u_id)
   WHERE A.proptype = C.uap_proptype AND A.price >= C.uap_price_min AND A.price <= C.uap_price_max
   AND A.id = 55


AW
0
 
fishtank22Author Commented:
Awesome! Thanks Arthur!

Not only does it work. But I think I understand it as well. I really appreciate it. My first question on Experts-Exchange was a great success!

Thanks again.

Fish
0
 
Arthur_WoodCommented:
glad to be of assistance.

AW
0
 
fishtank22Author Commented:
Arthur, one more quick question.

For a query like this where do I want to place indexes in the tables to ensure they are optimized properly.

Thanks again.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now