Test Case for Triple Join in SQL

Question

SELECT triple_join('A multiple join on the Equipment, EquipmentTypes, and
  RentalInfo works effectively: ',
    CASE WHEN
      (SELECT name, e.riid, rental_start, in_use FROM Equipment AS e JOIN 
        EquipmentTypes ON equipment_type = etid JOIN RentalInfo AS ri ON
        e.riid = ri.riid)
= ('Enabler 420', 1, '20141107', 1)
  THEN 'Passed' ELSE 'Failed' END
  );

This is the code that tests whether my join is working effectively. When I run the code, however, it says I have a syntax error near ",". Can someone tell me what's going on?

P.S.: The Equipment, EquipmentTypes, and RentalInfo are tables I have defined and 'inserted to' in another file.

The first guess is a parsing error on the string that spans two lines. You don't mention the database or how you are processing the query, so this is a possibility. — Gordon Linoff, May 01 '16 at 21:09
Is `triple_join` a function with two parameters? Does your DBMS support comparing multiple columns using a single `=`? Does the join really return a single row only? — dnoeth, May 01 '16 at 21:25
I followed the format described in this link: http://stackoverflow.com/questions/754527/best-way-to-test-sql-queries I tried writing the test case using all commands on the same line so I guess that's not the problem. Yes, the join only returns one row: Enabler 420|1|20141107|1 — Shubham Goenka, May 01 '16 at 21:34
@Shubham Goenka "I followed the format...". You didn't. You cannot compare tuples, only values. — Alex Kudryashev, May 01 '16 at 21:53
What DBMS are you using? Can you add a tag to indicate that? — red6, May 10 '16 at 05:09

score 0 · Answer 1 · answered Jun 12 '16 at 13:42

Not sure if it solves the problem, but it is much simpler to debug than what you had. Also, it will solve the problem of multiple matching records:

SELECT triple_join('A multiple join on the Equipment, EquipmentTypes, and
  RentalInfo works effectively: ',
    CASE WHEN EXISTS (
        SELECT TOP 1 1 FROM Equipment AS e 
        INNER JOIN EquipmentTypes as et ON e.equipment_type = et.etid 
        INNER JOIN RentalInfo AS ri ON e.riid = ri.riid
        WHERE name = 'Enabler 420' and e.riid = 1 and 
            rental_start = '20141107' and in_use = 1) 
    THEN 'Passed' ELSE 'Failed' END
);

If problem still persist try to run separately internal "SELECT" and the function and see what is causing the problem.

Test Case for Triple Join in SQL

1 Answers1