SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2006
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question how to write this query??? (nested? join table...)

    hi, i've treid on few queries for the below statement but they do not echo the right thing...

    may i know how to write the query for the below statment?


    select * from table1 where gender='m' and select * from table2 where gender='f' order by date

    i want to display male from table 1 and female from table 2 order by their join date...

  2. #2
    SitePoint Zealot amrknt's Avatar
    Join Date
    Nov 2003
    Location
    india
    Posts
    192
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    select * from table1,table2 where table1.gender='m' and table2.gender='f'

    try that

  3. #3
    SitePoint Zealot
    Join Date
    Dec 2006
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by amrknt View Post
    select * from table1,table2 where table1.gender='m' and table2.gender='f'

    try that
    it shows Unknown column table2.gender' in 'where clause'

  4. #4
    SitePoint Wizard silver trophy
    Join Date
    Mar 2006
    Posts
    6,132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you cannot expect to use a table or column which doesnt exist.

  5. #5
    SitePoint Zealot
    Join Date
    Dec 2006
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok.. got it..

    thanks!

  6. #6
    SitePoint Zealot
    Join Date
    Dec 2006
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $query="select * from holiday where exists(SELECT * FROM records WHERE state_id=$state)order by date";
    what about the below code?

    it shows: You have an error in your SQL syntax near 'exists(SELECT * FROM records WHERE state_id=12)order by date' at line 1

    why is this happen?

  7. #7
    SitePoint Zealot
    Join Date
    Dec 2006
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i try on this also:

    SELECT * FROM records, holiday where records.holiday_id=holiday.id and records.state_id=$state or records.holiday_id=holiday.id and records.state_id=$all order by date

    however,
    or records.holiday_id=holiday.id and records.state_id=$all order by dateseems not executed... why?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you know, you really should ask this type of question in the databases or mysql forum
    Code:
    select col1
         , col2
         , col3
         , datecol
      from table1 
     where gender='m' 
    union all
    select column1
         , column2
         , column3
         , datecolumn
      from table2 
     where gender='f' 
    order 
        by datecol
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •