SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    E-business guru Eirik's Avatar
    Join Date
    Nov 2000
    Location
    Oslo, Norway
    Posts
    413
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL select challenge

    Hi,

    I'm having some trouble creating a SQL query to fit my needs. Here's the challenge.

    I have a table looking like this:


    Code:
    +-----+------------------+-------------+---------+
    | id | contentobject_id | parent_node | is_main |
    +-----+------------------+-------------+---------+
    | 190 |			1 |		 1 |	 1 |
    | 4 |			8 |		 5 |	 1 |
    | 5 |			42 |		 5 |	 1 |
    | 6 |			10 |		 44 |	 1 |
    | 191 |			42 |		 6 |	 1 |
    +-----+------------------+-------------+---------+


    Provided with a list of parent nodes, I need to fetch the contentobject_ids that are related to all the parent nodes. For instance, if the list of parent nodes was node 5 and 6, the query would return contentobject_id 42 which is the only id which is related to these two parent_nodes.

    Could someone point me in the right direction to building a SQL query to do this?

    Thanks in advance !
    Sincerely,

    Eirik Johansen
    Netmaking AS

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the way to approach it is like this

    first, you put parent nodes 5 and 6 into the WHERE clause, because those are the only rows that have a chance of qualifying

    now, after the WHERE clause has filtered the table rows to only the 5s and 6s, what you want is for a given contentobject_id to occur at least once for every parent node, so, since you know there were 2 parent nodes you were interested in, it looks like this:
    Code:
    select contentobject_id 
      from yourtable
     where parent_node in ( 5 , 6 )
    group 
        by contentobject_id 
    having count(distinct parent_node) = 2
    by grouping on contentobject_id, you can use aggregate functions in the HAVING clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    E-business guru Eirik's Avatar
    Join Date
    Nov 2000
    Location
    Oslo, Norway
    Posts
    413
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy,

    Thanks for coming to my SQL rescue yet again. You've saved my day!
    Sincerely,

    Eirik Johansen
    Netmaking AS


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
  •