CODEWITHSUNDEEP
mysqlsqlsubquery
pgendron
pgendron

Reputation: 25

Sub query with no result

I have two query that I would like to combine into one.

Query 1: SELECT Quantity FROM Table1 WHERE IdProduct = 1234

Query 2: SELECT Supplier FROM Table2 WHERE IdProduct = 1234

Here is what I have done:

SELECT
   bloc1.Quantity AS qty,
   bloc2.Supplier AS supplier
FROM    
   (SELECT Quantity FROM Table1 WHERE IdProduct = 1234) bloc1,
   (SELECT Supplier FROM Table2 WHERE IdProduct = 1234) bloc2

Most of the time everything goes well, but unfortunately, sometimes one of the two query return no result... In this specific case, the two query "fail".

Upvotes: 0

Views: 175

Answers (5)

Starnec
Starnec

Reputation: 587

U can join the two tables table1 and table2 u can do:

 SELECT qty,sup
    FROM   (SELECT Quantity as qty,supplier as sup
        FROM Table1 
        INNER JOIN Table2
        ON table1.IdProduct=table2.IdProduct AND IdProduct=1234);

u can check this for more information on joins in mysql https://www.mysqltutorial.org/mysql-join/

Upvotes: 1

forpas
forpas

Reputation: 164164

Your code is doing a CROSS JOIN of the results of the 2 queries.
So if any of them returns no rows, the result is no rows.

Instead use the result of the 2 queries as columns if you are sure that they will return only 1 row:

SELECT 
  (SELECT Quantity FROM Table1 WHERE IdProduct = 1234) qty,
  (SELECT Supplier FROM Table2 WHERE IdProduct = 1234) supplier

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

You can try the below -

select 
(SELECT Quantity FROM Table1 WHERE IdProduct = 1234) as qty,
(SELECT Supplier FROM Table2 WHERE IdProduct = 1234) as supplier

Upvotes: 0

The Impaler
The Impaler

Reputation: 48850

You probably want a "full outer join". Unfortunately MySQL does not implement full outer joins (why in this day and age?).

Anyway, you can simulate it with the workaround:

SELECT
   bloc1.Quantity AS qty,
   bloc2.Supplier AS supplier
FROM (SELECT Quantity FROM Table1 WHERE IdProduct = 1234) bloc1
LEFT JOIN (SELECT Supplier FROM Table2 WHERE IdProduct = 1234) bloc2 on 1 = 1
UNION
SELECT
   bloc1.Quantity AS qty,
   bloc2.Supplier AS supplier
FROM (SELECT Quantity FROM Table1 WHERE IdProduct = 1234) bloc1
RIGHT JOIN (SELECT Supplier FROM Table2 WHERE IdProduct = 1234) bloc2 on 1 = 1

In MySQL 8.x you can remove the redundancy (and somewhat shorten it) by using CTEs. For example:

with
bloc1 as (SELECT Quantity FROM Table1 WHERE IdProduct = 1234),
bloc2 as (SELECT Supplier FROM Table2 WHERE IdProduct = 1234)
SELECT
   bloc1.Quantity AS qty,
   bloc2.Supplier AS supplier
FROM bloc1 LEFT JOIN bloc2 on 1 = 1
UNION
SELECT
   bloc1.Quantity AS qty,
   bloc2.Supplier AS supplier
FROM bloc1 RIGHT JOIN bloc2 on 1 = 1

Upvotes: 0

chaos
chaos

Reputation: 124335

It's remarkable to me that slapping those queries together like that ever works. But if you want it to survive a lack of records in one of these tables, you'll need an OUTER JOIN of some kind. See https://dev.mysql.com/doc/refman/5.7/en/join.html.

Upvotes: 0

Related Questions