#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    2
    Rep Power
    0

    Subquery Syntax Error


    Hello,

    one of my queries doesnīt work. I got the errornumber 1064, syntax error. But I donīt know why.

    This query works:

    Code:
    			select distinct id_mat_bauteil from material_bestueckungslinien E where exists(
    					select id_mat_bauteil from(
    								select id_mat_bestueckungslinie from (
    									select id_mat_bestueckungslinie, id_material from material_musterbestueckungen A
    									union all
    									select id_mat_bestueckungslinie, id_material from material_sonderbestueckungen B) C
    								where C.id_material = 10154) D
    					where D.id_mat_bestueckungslinie = E.id_mat_bestueckungslinie)
    But this extended version not:


    Code:
    	Select * from  material_bauteile Z
    		where exists (
    			select distinct id_mat_bauteil from material_bestueckungslinien E where exists(
    					select id_mat_bauteil from(
    								select id_mat_bestueckungslinie from (
    									select id_mat_bestueckungslinie, id_material from material_musterbestueckungen A
    									union all
    									select id_mat_bestueckungslinie, id_material from material_sonderbestueckungen B) C
    								where C.id_material = 10154) D
    					where D.id_mat_bestueckungslinie = E.id_mat_bestueckungslinie) F
    			where F.id_mat_bauteil = Z.id_mat_bauteil);
    Any help appreciated.

    Peter
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    it's obvious

    in the first query, you used WHERE EXISTS and then the subquery following this does not have a table alias (which is correct)

    in the second query, you used WHERE EXISTS but then you gave the subquery following this the table alias of F (which is incorrect) and tried to append an ON condition (which is incorrect)

    i'd try to fix it for you, but it's so complicated that i don't understand what it's suppoed to be doing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    2
    Rep Power
    0

    Obvious :-)


    Originally Posted by r937
    it's obvious

    in the first query, you used WHERE EXISTS and then the subquery following this does not have a table alias (which is correct)

    in the second query, you used WHERE EXISTS but then you gave the subquery following this the table alias of F (which is incorrect) and tried to append an ON condition (which is incorrect)

    i'd try to fix it for you, but it's so complicated that i don't understand what it's suppoed to be doing
    Thanks for your answer. You are right. That works:

    Code:
    SELECT DISTINCT Z.* 
    FROM  material_bauteile Z   
    INNER JOIN material_bestueckungslinien E 
    ON Z.id_mat_bauteil = E.id_mat_bauteil
    INNER JOIN (SELECT id_mat_bauteil FROM material_musterbestueckungen WHERE id_material = 10154
                UNION 
                SELECT id_mat_bauteil FROM material_sonderbestueckungen WHERE id_material = 10154) C
    ON E.id_mat_bauteil = C.id_mat_bauteil
    and this also
    Code:
    Select * from  material_bauteile Z
        where exists (
            select distinct id_mat_bauteil from material_bestueckungslinien E where exists(
                    select id_mat_bauteil from(
                                select id_mat_bestueckungslinie from (
                                    select id_mat_bestueckungslinie, id_material from material_musterbestueckungen A
                                    union all
                                    select id_mat_bestueckungslinie, id_material from material_sonderbestueckungen B) C
                                where C.id_material = 10154) D
                    where D.id_mat_bestueckungslinie = E.id_mat_bestueckungslinie) AND id_mat_bauteil = Z.id_mat_bauteil)
    Last edited by PeterSchall; June 12th, 2013 at 02:39 PM. Reason: Resolved
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by PeterSchall
    There are three tables now.
    items
    itemsets
    material
    please do a SHOW CREATE TABLE for each one, so that we can see the foreign keys as well

    Originally Posted by PeterSchall
    I need to update all material which contains in its own itemset the just changed material itemset.
    i know you said this in english, but i don't understand it at all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo