SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    May 2009
    Location
    Sydney Australia
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    sub query, case statement, if?

    I have a table
    Parent |child1_Name|child1_class|child2_name|child2_class
    Smith |George | 1SE |Fred |4WZ
    Jones |Mary | 4WZ

    how could I get a class list of the names of all the children in class 4WZ?

    somehow combining

    Code:
    SELECT child1_Name FROM tbl_parents WHERE child1_Name =4WZ
    with
    Code:
    SELECT child2_Name FROM tbl_parents WHERE child2_Name =4WZ
    is this possible?

    Thanks
    biencreative.com.au

    Web Design for Small Business on Sydney's North Shore

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    700
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select parent,child1_name
      from tbl_parents
     where child1_class = '4WZ'
     union all
    select parent,child2_name
      from tbl_parents
     where child2_class = '4WZ'

  3. #3
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    combining

    Code:

    SELECT child1_Name FROM tbl_parents WHERE child1_Name =4WZ

    with
    Code:

    SELECT child2_Name FROM tbl_parents WHERE child2_Name =4WZ
    Use Mysql UNION

  4. #4
    SitePoint Member
    Join Date
    May 2009
    Location
    Sydney Australia
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your useful and quick responses that works just great
    What a great forum.
    biencreative.com.au

    Web Design for Small Business on Sydney's North Shore

  5. #5
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Helen, your table needs normalized, take some time to read up on Database Normalization, it is an important concept to understand.

  6. #6
    SitePoint Member
    Join Date
    May 2009
    Location
    Sydney Australia
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Unfortunately it not "my" database table It's from a joomla component. I was trying to work out whether it was easier to change the data input i.e use Joomla to create an extra table or output i.e get a query that will give me the data from tables that i wouldn't necessarilry have set up in this way.
    biencreative.com.au

    Web Design for Small Business on Sydney's North Shore


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
  •