By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,666 Members | 1,815 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,666 IT Pros & Developers. It's quick & easy.

select multiple columns from multiple tables with different number of columns

omerbutt
100+
P: 638
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
  1. SELECT DISTINCT 
  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
  22.  
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
May 12 '08 #1
Share this Question
Share on Google+
2 Replies


P: 31
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

100+
P: 310
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.
Aug 8 '08 #3

Post your reply

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