How can I select the opposit of a select statement

Hi
I try to select the opposite of the following sql statement:

SELECT b.pages_page_elements_id, a.page_id, b.sort, c.element_id , c.element_name
            FROM pages a
            INNER JOIN pages_page_elements b ON a.page_id = b.page_id
            INNER JOIN page_elements c ON b.element_id = c.element_id
            WHERE a.page_name IN ('home')
            ORDER BY b.sort ASC

I tried the following, but I get the following error in phpmyadmin "Operand should contain 1 column(s) "

SELECT *
      FROM page_elements a
        INNER JOIN pages_page_elements b ON b.element_id = a.element_id
      INNER JOIN pages c ON c.page_id = b.page_id
      WHERE b.element_id NOT IN(
            SELECT a.element_id, a.element_name, b.sort, b.pages_page_elements_id, c.page_id
            FROM page_elements a
              INNER JOIN pages_page_elements b ON b.element_id = a.element_id
            INNER JOIN pages c ON c.page_id = b.page_id
              WHERE c.page_name IN ('home')
            ORDER BY b.sort ASC)

Thanks for a hint :-)
Roger
hirtisAsked:
Who is Participating?
 
PadawanDBAOperational DBACommented:
I'm confused...  If you want the "opposite" of the result set being returned by your initial query, why don't you just do this in the where clause ?

SELECT 
     b.pages_page_elements_id, 
     a.page_id, 
     b.sort, 
     c.element_id ,
     c.element_name
FROM 
     pages as a 
            INNER JOIN pages_page_elements as b ON a.page_id = b.page_id 
            INNER JOIN page_elements as c ON b.element_id = c.element_id
WHERE 
     a.page_name not IN ('home')
ORDER BY 
     b.sort ASC

Open in new window

0
 
ste5anSenior DeveloperCommented:
It's exactly what the error messages says: Only one column.

E.g.

SELECT  *
FROM    page_elements a
        INNER JOIN pages_page_elements b ON b.element_id = a.element_id
        INNER JOIN pages c ON c.page_id = b.page_id
WHERE   b.element_id NOT IN ( SELECT    a.element_id
                              FROM      page_elements a
                                        INNER JOIN pages_page_elements b ON b.element_id = a.element_id
                                        INNER JOIN pages c ON c.page_id = b.page_id
                              WHERE     c.page_name IN ( 'home' )
                              ORDER BY  b.sort ASC )

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.