CODEWITHSUNDEEP
sqldatabasedb2
Andy Marshal
Andy Marshal

Reputation: 11

Rewriting Joins as Subqueries in this query

Can you guys pls help me rewrite this using a subquery

SELECT 
    S.DENUMIRE, M.DENUMIRE, AN
FROM 
    SPECIALIZARE S
JOIN 
    MATERII M ON S.CODSPEC = M.CODSPEC
ORDER BY 
    S.DENUMIRE

Upvotes: 0

Views: 897

Answers (4)

Leo
Leo

Reputation: 1514

Do you just want distinct rows from both tables?

SELECT
    SQ.DENUMIRE, SQ.AN
FROM (
    SELECT
        DENUMIRE, AN
    FROM 
        SPECIALIZARE

    UNION

    SELECT
        DENUMIRE, AN
    FROM 
        MATERII
) SQ
ORDER BY
    SQ.DENUMIRE

Upvotes: 0

dan1111
dan1111

Reputation: 6566

You might be able to do something silly with a union and a group by. For example:

select max(s_denumire) as s_denumire,
       max(m_denumire) as m_denumire,
       codspec
    from (
        select denumire as s_denumire, null as m_denumire, codspec 
            from specializare
        union all
        select null as s_denumire, denumire as m_denumire, codspec 
            from materii
    )
    group by codspec

This produces one row per codspec, so it's not exactly like a join. But you could probably do something with row numbers to address that. I'll leave that as an exercize for you, since this is evidently homework.

Upvotes: 0

onedaywhen
onedaywhen

Reputation: 57093

Assuming DENUMIRE is an integer:

SELECT S.DENUMIRE, 1 DENUMIRE, AN
  FROM SPECIALIZARE S
 WHERE EXISTS (
               SELECT * 
                 FROM MATERII M 
                WHERE S.CODSPEC = M.CODSPEC
                      AND M.DENUMIRE = 1
              )
UNION
SELECT S.DENUMIRE, 2 DENUMIRE, AN
  FROM SPECIALIZARE S
 WHERE EXISTS (
               SELECT * 
                 FROM MATERII M 
                WHERE S.CODSPEC = M.CODSPEC
                      AND M.DENUMIRE = 2
              )
UNION
SELECT S.DENUMIRE, 3 DENUMIRE, AN
  FROM SPECIALIZARE S
 WHERE EXISTS (
               SELECT * 
                 FROM MATERII M 
                WHERE S.CODSPEC = M.CODSPEC
                      AND M.DENUMIRE = 3
              )

UNION
...

and so on for every integer value... which of course is not feasible.

You need a join! If you simply want to avoid avoid the JOIN keyword for pedagogical reasons:

SELECT 
    S.DENUMIRE, M.DENUMIRE, AN
FROM 
    SPECIALIZARE S, MATERII M 
WHERE
    S.CODSPEC = M.CODSPEC
ORDER BY 
    S.DENUMIRE

p.s. although SQL's "shackles of compatibility" (a vendor back in the 1980s supported this and it became standard etc) allows two columns in the same table to have the same name it is probably not a good idea in practice. Also, I'm assuming AN is from table SPECIALIZARE but could be a typo.

Upvotes: 0

silly
silly

Reputation: 7887

SELECT 
    S.DENUMIRE,
    M.DENUMIRE,
    AN
FROM SPECIALIZARE S
JOIN (
    SELECT
        DENUMIRE,
        CODSPEC
    FROM MATERII
) AS M
    ON S.CODSPEC = M.CODSPEC
ORDER BY S.DENUMIRE

Upvotes: 1

Related Questions