Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 )
share|improve this question
1  
SELECT DISTINCT –  Phil Oct 6 '12 at 15:51
    
Thanks for your reply but DISTINCT not working –  shahriar Oct 6 '12 at 21:13
    
add 'GROUP BY TBLA.Mobile, TBLB.factor_id' at the end? –  Max Vernon Oct 6 '12 at 22:14
    
Do you want each factor_id showing up in the return list? I'm assuming that's the invoice number or something that changes for each invoice. If you want each one then you're going to need to get give results or combine the list of factor_ids into a single row. –  cfradenburg Oct 6 '12 at 22:22
3  
What does "not working" mean? –  Aaron Bertrand Oct 6 '12 at 22:34
show 1 more comment

1 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;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.