select from multiple tables please help

[php-lover]

Hi,

It's took me a weeks to figure out this query.

I'm trying to select all the data from 7 tables but i got empty result. All these table has a link on region_id column.

Your help will be much much appreciated.

here's my query:

$q = 'SELECT rs.*,rr.*,cs.*,cr.*,cl.*,ls.*,ll.* FROM 
               tbl_residential_sale as rs,
               tbl_residential_rent as rr,
               tbl_commercial_sale as cs,
               tbl_commercial_rent as cr,
               tbl_commercial_lease as cl,
               tbl_land_sale as ls,
               tbl_land_lease as ll
               WHERE 
               rs.start_date IS NOT NULL AND rs.expire_date >= NOW() AND rs.region_id=1
               AND
               rr.start_date IS NOT NULL AND rr.expire_date >= NOW() AND rr.region_id=1
               AND
               cs.start_date IS NOT NULL AND cs.expire_date >= NOW() AND cs.region_id=1
               AND
               cr.start_date IS NOT NULL AND cr.expire_date >= NOW() AND cr.region_id=1
               AND
               cl.start_date IS NOT NULL AND cl.expire_date >= NOW() AND cl.region_id=1
               AND
               ls.start_date IS NOT NULL AND ls.expire_date >= NOW() AND ls.region_id=1
               AND
               ll.start_date IS NOT NULL AND ll.expire_date >= NOW() AND ll.region_id=1';

If you need more information about my table then I can post it.

Thank you for your time.

Finau

[tesuji]

Hi Finau,

If both following statements are true

1. Each table has a column named region_id of compatible data type
2. At least one of those tables has primary key region_id AND all other
tables containing region_id that column is attributed to be foreign key.

then you may carry out this sql statement

select * from  tbl_residential_sale as rs join tbl_residential_rent as rr
         join  tbl_commercial_sale as cs join tbl_commercial_rent as cr
         join  tbl_commercial_lease as cl join tbl_land_sale as ls
         join  tbl_land_lease as ll

to get a first result. Afterwards you can add some further predicates, like cr.start_date IS NOT NULL AND cr.expire_date >= NOW()..., to where-clause for reducing result set.

I really doubt a little that each table has column region_id. You may post the creating statements of your seven tables.

Btw, your select statement seriously lacks of join constraints which must be added to where-clause, as for example: where rs.joining-column = rr.joining-colum etc. If you omit such constraints you will get a undesirable cross products (cartesian products).

-----
tesu