0

I am creating my first left join and I am having syntax errors. I been up and down this list and I cannot figure what is the issue. Here is the setup

I have three tables Mass_List, Parent and Parent_Place. They all have the same ID but I need to get the Date_Close from Mass_List and the ID and Username from Parent, making sure that the user is a paying member, which is know by the column MBSHIP in Parent_Place.

I do the queries separate and they work but when I do the queries together it tells me my syntax is correct but gives me the following error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I searched online but nothing close to my case. Any idea what's wrong with my syntax? Here is the query

SELECT P.USERNAME, M.DATE_CLOSE, P.ID 
FROM MASS_LIST M, PARENT P
WHERE P.ID =
          (SELECT M.ID 
           FROM MASS_LIST M 
           INNER JOIN PARENT_PLACE PP ON PP.ID = M.ID
           WHERE PP.CLASS_USR = 'PAID' 
             AND M.DATE_CLOSE > getdate() 
             AND PP.MBSHIP > 0)
 AND M.DATE_CLOSE > GETDATE() ORDER BY M.DATE_CLOSE;
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
Tavo
  • 173
  • 1
  • 15
  • 1
    Can you post sample data of your tables? the error is clear, your query is returning more than one value and you cant compare a scalar with a vector. – Juan Carlos Oropeza Feb 24 '16 at 19:10
  • Don'y mix ansi-92 and pre ansi-92 standards (inner join vs , notation) – xQbert Feb 24 '16 at 19:15
  • @xQbert I dont know years .. but do you mean this? Promote the use of explict `JOIN` sintaxis, Aaron Bertrand wrote a nice article [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) about it. – Juan Carlos Oropeza Feb 24 '16 at 19:16
  • Yep. Most engines/compilers can handle one or the other but usually not both in the same query. In addition it seems that the subselect really isn't needed. an additional join to parent place should do the trick. but to know for certain, sample data and expected results would help. and reference for the name: http://stackoverflow.com/questions/334201/why-isnt-sql-ansi-92-standard-better-adopted-over-ansi-89 – xQbert Feb 24 '16 at 19:18

1 Answers1

0

I'm not sure why you need the subslect in the first place... Just join all 3 tables together... Though I do find it odd that the "ID" column in each tables is what joins them together. Seems like your trying to join on the PK of each table which isn't the way they should be joined unless each table is truly a 1-1 relationship with the other... then I'd say interesting table design.

SELECT ML.Date_Close, P.ID, P.UserName
FROM mass_List ML
INNER JOIN PARENT P
 on ML.ID = P.ID
INNER JOIN PARENT_PLACE  PP
 on P.ID = PP.ID
WHERE PP.MBSHIP > 0
  and PP.Class_user = 'PAID'
  and M.Date_Close > getDate()
order by m.date_Close
xQbert
  • 34,733
  • 2
  • 41
  • 62