select multiple columns from multiple tables with different number of columns

omerbutt

hi there i have to select 7 columns from table1, 1 column from table2, and 1 column from tables3 and show the result but i am not getting it right the main thing that i am trying to achieve is that i am making a notice board in a community and i have to show the notices that are posted for the specific user by comparing the fields like GENDER AGE AND CITY that are given in the notice i have done it quiet right to some extent means if i have to compare only
age (between 19 and 26)
and
gender (Male only )
OR
gender(Female only)
but what if THE NOTICE IS ABOUT BOTH THE GENDERS MALE AND FEMALE then it creates a problem the
notice description, title ,notice gender,notice city ,notice start age, notice end age and notice expiry are comming from
notices table
and the members age is comming from the
member_profile table
and the gnder is comming from the
members table
iwrote the query

Expand|Select|Wrap|Line Numbers

 
SELECT DISTINCT 

    rage_notice.notice_startdate, 

    rage_notice.notice_enddate, 

    rage_notice.notice_id, 

    rage_notice.member_id, 

    rage_notice.notice_title, 

    rage_notice.notice_description, 

    rage_notice.notice_age_start, 

    rage_notice.notice_age_end,

    rage_notice.notice_sex, 

    rage_notice.notice_country, 

    rage_notice.notice_city,

    rage_members_profile.members_profile_age,

    rage_members.member_type 

FROM rage_notice,rage_members_profile,rage_members 

WHERE (rage_notice.notice_enddate >=  '$date' and rage_members_profile.member_id=$member_id) 

AND rage_notice.member_id != $member_id 

AND rage_members_profile.members_profile_age >= rage_notice.notice_age_start 

AND rage_members_profile.members_profile_age <= rage_notice.notice_age_end 

AND (rage_members.member_type=rage_notice.notice_sex) 

ORDER BY rage_notice.notice_stamp desc

this query works for the case if the notice posted is for the Males and it works perfect but when i try to add the rage_notice.notice_sex='Both' with the following line @ very last line of the query (rage_members.member_type=rage_notice.notice_sex || rage_notice.notice_sex ='Both') it do picks up the exact records but shows those notices twice where gender='Both' although i am using distinct keyword but it will not work in three tables any suggesstions for that
thanks for any help in advance
regards,
omer

BHTanna

Distinct key word work with n no of tables..

But i feel, your select statement has 2 fields (i.e member_type, sex), which i guess has 2 different values.. So, it is giving record twice when u add this extra condition. Remove either field...

Hope it works..

coolsti

I am not going into detail at all in your query problem because I do not know the structure of the tables.

However, one thing I do note: You are joining three tables, yet you only show one equality between the tables in your where clause, in this case

[code]
AND (rage_members.member_type=rage_notice.notice_sex)
[\code]

In general, if you are joining N tables, you need N-1 equalities between the tables. You are joining 3 tables so I would expect you need 2 equalities for a proper join.

This might be hidden by the fact that you use the Distinct keyword. I am just wondering if the missing equality will result in your query giving you the wrong results.

select multiple columns from multiple tables with different number of columns

Post your reply

Similar topics