Sql code showing the answer 2 times

Question

SELECT p.*, po.OrderedQty, po.TotalAmount 
FROM tblPurchaseOrder  AS p
INNER JOIN PODetails  AS po ON p.PONo = po.PONo
WHERE po.PONo = '1';

I have 2 rows to display for this code, when I run this, it returns 4 rows, 2 original and 2 duplicates (same as first 2 rows) actual answer is only two rows. Why am I getting like this?

Please help me to know.

There are 2 tables, tblpurchaseorder and podetails. I need only one row from podetails table. But the confusing thing is why it shows only 4 rows, return.

4   1   1   2014-04-24  2014-04-24  Whole Sale Price                1   Nokia001    0   0   0   190 570 3   918
5   1   1   2014-04-24  2014-04-24  Whole Sale Price                1   item 1  0   0   0   87  348 3   918
4   1   1   2014-04-24  2014-04-24  Whole Sale Price                1   Nokia001    0   0   0   190 570 4   918
5   1   1   2014-04-24  2014-04-24  Whole Sale Price                1   item 1  0   0
0   87  348 4   918

tblpurchaseorder

POID    PONo    POReference CreatedDate DeliveryDate    PaymentMethod    SupplierID ItemCode    IsCommit    IsPending   IsDelete    SubTotal    TotalValue
4         1        1        2014-04-24   2014-04-24   Whole Sale Price        1     Nokia001        0           0         0            190            570
5         1        1        2014-04-24   2014-04-24   Whole Sale Price        1     item 1          0           0         0             87            348

tbl podetails

PODId   PONo    OrderedQty  SupplierID  TotalAmount PriceLevel        PaidAmount
3        1           3           1          918     Whole Sale Price      900
4        1           4           1          918     Whole Sale Price      900

You have incomplete `JOIN` criteria.. ie each PONo matches to 2 PONo's in the other table. — Hart CO, Apr 24 '14 at 20:26
Can you please show us the data in the two tables in question, and please also let us know what **database** you're using (SQL is just the **query language** - but not a database system). And please **do not** put code samples or sample data into comments - since you cannot format it, it's **extremely hard** to read it.... Instead: **update** your question by editing it to provide that additional information! Thank you. — marc_s, Apr 24 '14 at 20:27
@NAJEEB please add some column headers so we know what the data represents. The data you show appears to be four distinct records. To help you we will also need to know what columns each table has — Marshall Tigerus, Apr 24 '14 at 20:33
The duplicate fields are not the same - some have 3 and some have 4 for the last element in `tblPurchaseOrder` — Siyual, Apr 24 '14 at 20:33
3 and 3 , 4 and 4 ...its same data,if i check select * from purchaseorder i can find only 2 rows. — NAJEEB, Apr 24 '14 at 20:35
Can you please also add some **labels** to those lines of data? Which is which table? Which is the resulting output?? — marc_s, Apr 24 '14 at 20:49
where pono=1 i want the return... from both table. if you have suggestion am ready to change my database structure, but same data values. Purchaseorder table , POid is primary key , PONo is foreign key. PoDetails table PODId is primary key , PONo is foreign key... may be the database design is the problem. please fix it for me. — NAJEEB, Apr 24 '14 at 22:12
@NAJEEB Why are there two rows for each PONO in both the tables? If PONO is FK on both the tables, which table are they referencing? A 3rd table? Is there any relationship between PoDetails .PODID and tblPurchaseorder.POid? — Joseph B, Apr 25 '14 at 01:59
I suggest you go see that : http://stackoverflow.com/questions/621884/database-development-mistakes-made-by-application-developers?page=1&tab=votes#tab-top — Scott, Jul 30 '14 at 18:44

score 0 · Answer 1 · answered Aug 11 '14 at 12:02

You need to have a column or a combination of columns that must be unique in order to identify the records needed.

The result is correct... He take first line from Order Table and related to podetails! Here he findes 2 rows matches. Then he takes the second line from orders and related with the same 2 rows from podetails, so he will return 4 rows.

score -1 · Answer 2 · answered Apr 24 '14 at 20:26

-1

Guess the missing word is SELECT DISTINCT :)

answered Apr 24 '14 at 20:26

troligtvis

133
2
8

2

Distinct may be exactly the worst thing to do in this case – HLGEM Apr 24 '14 at 20:32

Marshall Tigerus · Answer 3 · 2014-04-24T20:54:48.130

-1

The cheap solution is to add a Select Distinct in there to only return distinct records (which would likely remove your duplication problem). This is not always great solution, however.

Post what records get returned, and what you should be getting back, and we can look to fix it (though distinct will probably fix the problem for now)

EDIT: Looking at your results, you have four distinct records. It looks like (assuming the ordering is the same as your select statement) the difference is int eh po.OrderedQty column and po.TotalAmount column

Since you state in the comments that you run the query select * from tblPurchaseOrder, and only get 2 results, it is apparent that the two columns from PODetails are what is causing the additional records (and this is CORRECT for the join as you have it).

What you have is this in the tblPurchaseOrder (truncated to one column):

Nokia
Item 1

And this in the PODetails:

3
4

and you get:

Nokia 3
Item 1 3
Nokia 4
Item 1 4

The real problem is that your POno, which you are using as your join, is 1 for all your records. This makes the results i demonstrate above happen, because 1 will equal 1 for each record.

You do not have columns between these two tables that will uniquely identify the records you want.

edited Apr 24 '14 at 20:54

answered Apr 24 '14 at 20:28

Marshall Tigerus

3,675
10
37
67

1

Distinct may be exactly the worst thing to do in this case – HLGEM Apr 24 '14 at 20:30
1

@HLGEM which is why I asked for data – Marshall Tigerus Apr 24 '14 at 20:31
yea . it shoould be 2 records, not four. – NAJEEB Apr 24 '14 at 20:38
@NAJEEB try the query above – Marshall Tigerus Apr 24 '14 at 20:45
Msg 2714, Level 16, State 6, Line 2 There is already an object named 'tblPurchaseOrder' in the database. Msg 2714, Level 16, State 4, Line 2 There is already an object named 'FK_tblPurchaseOrder_tblPurchaseOrder' in the database. Msg 1750, Level 16, State 0, Line 2 Could not create constraint. See previous errors. Msg 207, Level 16, State 1, Line 14 Invalid column name 'PODId'. – NAJEEB Apr 24 '14 at 20:49
can you post results from select * from PODetails – Marshall Tigerus Apr 24 '14 at 20:50
PONo is foreign key....but i made the POId primary key for purchaseorder table and , podid primary key for podetails table.... – NAJEEB Apr 24 '14 at 20:53
@NAJEEB you can't do what you want from these two tables. There is not fields present between them that can uniquely identify them the way you want – Marshall Tigerus Apr 24 '14 at 20:55
but what about PONo? in both tables we can find that row. – NAJEEB Apr 24 '14 at 20:58
@NAJEEB depends if you want 2 results (which is what your original question and the concept I've been working on this whole time) or 1. if you want 2, I don't see a way to do it that is maintainable (certainly we can force it). If you want 1, go with Joesph's answer – Marshall Tigerus Apr 24 '14 at 21:00
actually it is 2 rows , not 1. or 4. i am getting 4 rows . i guess you are almost there. i know i am doing mistake but i dont know what is that. – NAJEEB Apr 24 '14 at 21:04
how i can get 2 rows , that you are talking about forcing... please give me the query for that. – NAJEEB Apr 24 '14 at 21:18

Joseph B · Answer 4 · 2014-04-24T20:57:42.410

-1

There are likely multiple rows in the podetails table for each PoNo. That is why you get multiple rows.

SELECT * 
FROM PODetails po
WHERE po.PONo = '1';

Therefore, you need to join using another column as well (POID), as below:

SELECT p.*, po.OrderedQty, po.TotalAmount 
FROM tblPurchaseOrder  AS p
INNER JOIN PODetails  AS po ON p.PONo = po.PONo AND po.PODID = p.POID
WHERE po.PONo = '1';

edited Apr 24 '14 at 20:57

answered Apr 24 '14 at 20:51

Joseph B

5,519
1
15
19

This query will not return the values he wants, it will return only one record – Marshall Tigerus Apr 24 '14 at 20:54
@MarshallTigerus Yes, the OP wants only one record. Please see the updated question. – Joseph B Apr 24 '14 at 20:55
SELECT * FROM PODetails po WHERE po.PONo = '1'; in code , shows correct answer – NAJEEB Apr 24 '14 at 20:57
@JosephB I see nowhere where it says he wants one record. I see "I have 2 rows to display for this code, when I run this, it returns 4 rows, 2 original and 2 duplicates (same as first 2 rows) actual answer is only two rows" – Marshall Tigerus Apr 24 '14 at 20:57
@MarshallTigerus Please see "I need only one row from podetails table. But the confusing thing is why it shows only 4 rows, return." towards the end. – Joseph B Apr 24 '14 at 20:58
@Najeeb Try running the last query in my answer. You will see the one record for POID = 4 and PONO = 1. – Joseph B Apr 24 '14 at 20:59
@joseph yea it return only one row... but what about the second row.? – NAJEEB Apr 24 '14 at 21:02
@NAJEEB It will return only one row because only POID = 4 is in both tables. – Joseph B Apr 24 '14 at 21:10
@NAJEEB Please see my comment for more details in the question. – Joseph B Apr 25 '14 at 02:00

Sql code showing the answer 2 times

4 Answers4