CODEWITHSUNDEEP
mysqlsqlsubquery
KDhanji
KDhanji

Reputation: 45

SQL Select Query between 3 tables

I'm having trouble on creating a query to pull data from 3 tables.

I have a job_skus table, skus table, and stage table, and I'm trying to select all the job skus for the whole month regardless of jobNo, for example to show a list of all job skus for the month of April.

The tables' structure is 

Job_Sku                             example data
  JobNo                             J4454             J4454
  Sku (refences Product.Sku)        93339             9947
  Quantity                          500               600
  Stage                             1                 2

Products
   Sku(primary key)                 93339             9947
   Description                      Galvanised Nails  Steel Rods 900mm

Stage
 jobNo                              J4454             J4454
 stage                              1                 2
 date                               04/04/2015        12/04/2015

and so on.

I have come up with this query

SELECT jm.sku,jm.quantity,p.description 
FROM stage as s, products as p, job_sku as jm 
WHERE s.Date BETWEEN '2015-04-01' AND '2015-04-30' 
AND jm.stage = s.stage AND p.sku = jm.sku

but it seems to be getting duplicate data in the query. Do I need to use a join of some sort, or maybe extract all the stages for that date period and join the result onto the job_sku table?

Upvotes: 1

Views: 193

Answers (3)

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7181

Here's one suggestion. Others in the thread have suggested distinct that will result in the same thing, but using an exists predicate clarifies the intention of the query (IMO):

SELECT jm.sku
     , jm.quantity
     , p.description 
FROM  products as p
JOIN  job_sku as jm
    ON p.sku = jm.sku
WHERE EXISTS (
    SELECT 1
    FROM stage s
    WHERE s.Date BETWEEN '2015-04-01' AND '2015-04-30' 
      AND jm.stage = s.stage
);

As others also have mentioned, ANSI joins will make the query a bit easier to understand. Vendors will continue to support the "," join for as long as we live, so the code wont break, but ANSI join will make it easier to maintain.

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82504

First, use proper joins. the join style you use is deprecated.
Explicit joins are a part of ANSI SQL for over 20 years now.

Second, if you get the correct results but some rows are duplicated, all you need to add is DISTINCT

SELECT DISTINCT jm.sku,jm.quantity,p.description 
FROM stage as s 
INNER JOIN job_sku as jm ON(jm.stage = s.stage)
INNER JOIN products as p ON(p.sku = jm.sku)
WHERE s.Date BETWEEN '2015-04-01' AND '2015-04-30'

Upvotes: 5

xNeyte
xNeyte

Reputation: 622

SELECT distinct jm.sku,jm.quantity,p.description 
FROM stage as s, products as p, job_sku as jm 
WHERE s.Date BETWEEN '2015-04-01' AND '2015-04-30' 
AND jm.stage = s.stage AND p.sku = jm.sku

Upvotes: 2

Related Questions