问题描述:

I have the following sql that I execute in SQL Server. I get a product of rows in the tx table. tx table has 6 rows in it and appt table has no matching row. master table has one row with personal info, but when I execute this sql I get 36 rows instead of getting one row for each record. I get the row times number of rows for this guy. I know I need another condition in the where clause but nothing I have tried works:

SELECT "tx"."today",

"tx"."ada_no",

"tx"."tth_no",

"tx"."surface",

"tx"."billed",

"tx"."ins_pays",

"tx"."ins_pays1",

"tx"."balance1",

"tx"."dr_no",

"tx"."status",

"tx"."coma",

"tx"."comb",

"tx"."notes",

"tx"."appt",

"tx"."w_date",

"tx"."thekey",

"tx"."service",

"tx"."time",

"appt"."doa",

"master"."wip",

LEFT(ampm, 1) AS premed,

"master"."med_hx",

"tx"."comc",

"tx"."comd",

"tx"."i_bal2",

"tx"."ins_pays2",

"tx"."i_bal3",

"tx"."ins_pays3"

FROM "tx"

LEFT OUTER JOIN "appt"

ON "tx"."appt_key" = "appt"."appt_key",

"master"

LEFT OUTER JOIN "tx" tx1

ON "master"."master_key" = "tx1"."master_key"

WHERE "master"."master_key" = CAST(71563.00 AS NUMERIC(10, 2))

AND "master"."master_key" = "tx"."master_key"

ORDER BY "tx"."time" ASC,

"tx"."appt" ASC

网友答案:

It's hard to say without seeing your data, but I'm guessing that you are seeing these results because tx is left joining on itself.

I am having a hard time understanding what exactly you need, but if include your current data and your desired output we could probably point you in the right direction.

On a side note, I don't see any reason for you to have all those double quotes in there. If you remove those and format your query it will be much easier for people here to help you out.

网友答案:

Try this

FROM "tx" LEFT OUTER JOIN "appt" ON "tx"."appt_key" = "appt"."appt_key" inner join
"master" 
on "master"."master_key" = CAST(71563.00 AS NUMERIC(10,2))  
AND "master"."master_key" = "tx"."master_key" 
LEFT OUTER JOIN "tx" tx1 ON "master"."master_key" = "tx1"."master_key"      

ORDER BY "tx"."time"          ASC,           "tx"."appt"          ASC 
网友答案:

Part of your issue is that you are missing JOIN types - you are using JOIN syntax and you are also joining the tables with a comma. You syntax should be like the following:

FROM tx 
LEFT OUTER JOIN appt 
  ON tx.appt_key = appt.appt_key
LEFT JOIN master 
   ON master.master_key = tx.master_key
LEFT OUTER JOIN tx tx1
  ON master.master_key = tx1.master_key

So your full query would be:

SELECT  tx.today
  , tx.ada_no 
  , tx.tth_no 
  , tx.surface
  , tx.billed 
  , tx.ins_pays 
  , tx.ins_pays1 
  , tx.balance1 
  , tx.dr_no
  , tx.status 
  , tx.coma
  , tx.comb 
  , tx.notes 
  , tx.appt 
  , tx.w_date 
  , tx.thekey 
  , tx.service 
  , tx.time
  , appt.doa 
  , master.wip
  , left(ampm,1) as premed
  , master.med_hx
  , tx.comc
  , tx.comd 
  , tx.i_bal2
  , tx.ins_pays2
  , tx.i_bal3
  , tx.ins_pays3
FROM tx 
LEFT OUTER JOIN appt 
  ON tx.appt_key = appt.appt_key
LEFT JOIN master 
   ON master.master_key = tx.master_key
LEFT OUTER JOIN tx tx1
  ON master.master_key = tx1.master_key
WHERE master.master_key = CAST(71563.00 AS NUMERIC(10,2)) 
ORDER BY tx.time ASC
  , tx.appt ASC
网友答案:

Don't mix old style and new style JOINs. In fact, you should avoid old style joins using commas entirely. It's not ANSI standard and can lead to confusing code. According to SQL "master" is being included as a CROSS JOIN to "tx" because you have nothing that relates it to the tx table. The cross join will cause the number of rows returned to be the multiplication product of the two pieces as each repeat for every record. Effectively your current query is this:

SELECT     tx.today, tx.ada_no, tx.tth_no, tx.surface, tx.billed, tx.ins_pays, tx.ins_pays1, tx.balance1, tx.dr_no, tx.status, tx.coma, tx.comb, tx.notes, tx.appt, 
                      tx.w_date, tx.thekey, tx.service, tx.time, appt.doa, master.wip, LEFT(ampm, 1) AS premed, master.med_hx, tx.comc, tx.comd, tx.i_bal2, tx.ins_pays2, 
                      tx.i_bal3, tx.ins_pays3
FROM         tx LEFT OUTER JOIN
                      appt ON tx.appt_key = appt.appt_key CROSS JOIN
                      master LEFT OUTER JOIN
                      tx AS tx1 ON master.master_key = tx1.master_key
WHERE     (master.master_key = 71563.00) AND (master.master_key = tx.master_key)
ORDER BY tx.time, tx.appt
网友答案:

The comma just before the word master looks out of place to me.

FROM   "tx"        
LEFT OUTER JOIN "appt"          
  ON "tx"."appt_key" = "appt"."appt_key",   -- try without this comma     
"master"        
LEFT OUTER JOIN "tx" tx1          
  ON "master"."master_key" = "tx1"."master_key" 
网友答案:

Ah, the vagaries of typing an answer too quickly. The original query was generating too many records due to the structure of the join, which used a left outer construct. Obviously a cross join creates the cross product of each involved table.

相关阅读:
Top