get query from 4 tables

Question

I have three tables:

User (Table)
- Email
- Moblie
- User_id

Invoice_product (Table dbo.payware_factor_product )
- invoice_id
- Product_id

Invoice (Table dbo.payware_factor )
- User_id
- Mobile
- invoice_id

Each product has its own ID.

I want a query that, for example for product_id 16, returns all customers who bought product 16 and the mobile number of customers only once.

But with the below query, if a customer has bought product 16 five times, the result shows his mobile number five times. How can I get it returned only once?

I wrote this query:

SELECT TBLA.mobile , TBLB.factor_id
FROM dbo.payware_factor_product TBLB
INNER JOIN dbo.payware_factor TBLA ON TBLB.factor_id = TBLA.factor_id
WHERE TBLB.product_id = 16
AND TBLA.mobile = (SELECT mobile )

Answer 1

There is something very wrong with the last line, which is a no-op (it has no effect being there).

AND TBLA.mobile = (SELECT mobile)

Because (SELECT mobile) creates a small subquery returning a single scalar column, which is, tada - TBLA.mobile. So that condition resolves to nothing, really.

That aside, you're getting something like this

Mobile          Factor_id
0312-1232132    1234
0312-1232132    1235
0312-1232132    1244
0312-1232132    1314

If the customer bought product_id 16 on 4 different invoices. DISTINCT on the entire row won't work. If you only need the mobile number to contact, then

  SELECT TBLA.mobile, MAX(TBLB.factor_id) LastFactorId
    FROM dbo.payware_factor_product TBLB
    JOIN dbo.payware_factor TBLA ON TBLB.factor_id = TBLA.factor_id
   WHERE TBLB.product_id = 16
GROUP BY TBLA.mobile

You can substitute MAX for MIN to get the first instance, or a complicated FOR XML subquery if you wanted all the factor_id's in a comma separated list. Or you could remove the column entirely.

Now, if your query is not as simple as shown, and you wanted everything from the B table, then you'll need to use ROW_NUMBER() to choose an individual row from B for a distinct row in A, like the following:

      SELECT *
        FROM
  (
      SELECT TBLA.mobile, TBLB.*, RN=row_number() over (partition by TBLA.mobile
                                                        order by TBLB.factor_id desc)
        FROM dbo.payware_factor_product TBLB
        JOIN dbo.payware_factor TBLA ON TBLB.factor_id = TBLA.factor_id
       WHERE TBLB.product_id = 16
    GROUP BY TBLA.mobile
  )        X
       WHERE RN=1;