467,925 Members | 2,008 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,925 developers. It's quick & easy.

select multiple columns from multiple tables with different number of columns

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)
gender (Male only )
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
  2.     rage_notice.notice_startdate, 
  3.     rage_notice.notice_enddate, 
  4.     rage_notice.notice_id, 
  5.     rage_notice.member_id, 
  6.     rage_notice.notice_title, 
  7.     rage_notice.notice_description, 
  8.     rage_notice.notice_age_start, 
  9.     rage_notice.notice_age_end,
  10.     rage_notice.notice_sex, 
  11.     rage_notice.notice_country, 
  12.     rage_notice.notice_city,
  13.     rage_members_profile.members_profile_age,
  14.     rage_members.member_type 
  15. FROM rage_notice,rage_members_profile,rage_members 
  16. WHERE (rage_notice.notice_enddate >=  '$date' and rage_members_profile.member_id=$member_id) 
  17. AND rage_notice.member_id != $member_id 
  18. AND rage_members_profile.members_profile_age >= rage_notice.notice_age_start 
  19. AND rage_members_profile.members_profile_age <= rage_notice.notice_age_end 
  20. AND (rage_members.member_type=rage_notice.notice_sex) 
  21. 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
May 12 '08 #1
  • viewed: 11101
2 Replies
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..
Aug 8 '08 #2
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

AND (rage_members.member_type=rage_notice.notice_sex)

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.
Aug 8 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Denis St-Michel | last post: by
17 posts views Thread by kalamos | last post: by
8 posts views Thread by carlospedr | last post: by
2 posts views Thread by jeffvh | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.