payments and trackingnumbers


CREATE TABLE [dbo].[trackingnumbers](
      [trackingnumberid] [int] IDENTITY(1,1) NOT NULL,
      [date] [datetime] NOT NULL CONSTRAINT [DF_trackingnumbers_date]  DEFAULT (getdate()),
      [orderid] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [trackingnumber] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [shipper] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [cost] [decimal](10, 2) NULL,
      [void] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [address] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK__trackingnumbers__2B3F6F97] PRIMARY KEY CLUSTERED
(
      [trackingnumberid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]



CREATE TABLE [dbo].[payments](
      [paymentid] [int] IDENTITY(1,1) NOT NULL,
      [orderid] [int] NOT NULL,
      [dateentered] [datetime] NULL,
      [datebilled] [datetime] NULL,
      [type] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [number] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [expiration] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [CV2] [int] NOT NULL,
      [confirmation] [varchar](70) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [resultcode] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [debit] [money] NOT NULL,
      [paymentinfo] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ebayquantity] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [paymentfee] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [email] [varchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [business] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_payments] PRIMARY KEY CLUSTERED
(
      [paymentid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]






I want every orderid from payments table that has type='visa'   that has an orderid in trackingnumbers table with more than one trackingnumber

but orderid is int in payments and varchar in trackingnumbers
LVL 1
rgb192Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ValentinoVConnect With a Mentor BI ConsultantCommented:
Here's the version with a name for column two (even though it's not used in the list of fields retrieved).
To clarify this a bit: the "list of fields retrieved" is found on line one in the code snippets.

SELECT P.OrderID
FROM dbo.payments P
INNER JOIN 
(    SELECT T.orderid, COUNT(orderid) as OrderCount
     FROM dbo.tracking T
     GROUP BY T.orderid
     HAVING COUNT(orderid) > 1
) T
ON T.orderid like CONVERT(VarChar(50), P.orderid)
WHERE P.type = 'visa';

Open in new window


And here's the version with the COUNT removed from the SELECT (as it's not used anyway):

SELECT P.OrderID
FROM dbo.payments P
INNER JOIN 
(    SELECT T.orderid
     FROM dbo.tracking T
     GROUP BY T.orderid
     HAVING COUNT(orderid) > 1
) T
ON T.orderid like CONVERT(VarChar(50), P.orderid)
WHERE P.type = 'visa';

Open in new window


If you compare both queries with the original one, you'll see the differences and learn how to name "calculated fields" in the process...

Hope that helps you a bit further?
0
 
ValentinoVBI ConsultantCommented:
Something like this?
select P.orderid
from dbo.payments P
where P.type = 'visa'
	and exists (select * from dbo.trackingnumbers TN 
			where TN.orderid = P.orderid
			having COUNT(*) > 1)

Open in new window

0
 
8080_DiverCommented:
I will try to refrain from commenting on the practice of using different datatypes to store data that is supposed to be the same. ;-)

Compare the execution plan for my query to the plan for ValentinoV's query and see which is better.  (Mine uses the currently advisable syntax, though. ;-)
SELECT P.OrderID
FROM dbo.payments P
INNER JOIN dbo.tracking T
ON P.orderid = CONVERT(VarChar(50), T.orderid)
WHERE P.type = 'visa';

Open in new window

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
rgb192Author Commented:
select P.orderid
from dbo.payments P
where P.type = 'visa'
      and exists (select * from dbo.trackingnumbers TN
                  where TN.orderid = P.orderid
                  having COUNT(*) > 1)



Msg 248, Level 16, State 1, Line 1
The conversion of the varchar value '190365396412HRT' overflowed an int column. Maximum integer value exceeded.


because orderid is int in payments and orderid is varchar with some non int values in tracknumbers
0
 
ValentinoVBI ConsultantCommented:
Wow, then I think there's an issue with the plan here.  How are you going to join the tables if the order IDs are not comparable?  (I assumed that only the data type was different)
0
 
8080_DiverCommented:
That's why I explicitly contraolled the conversion and converted the integer instead of allowing the implicit conversion that, all too often, will try to convert the VarChar to an integer.
0
 
rgb192Author Commented:
8080 diver



SELECT P.OrderID
FROM dbo.payments P
INNER JOIN dbo.trackingnumbers T
ON P.orderid = CONVERT(VarChar(50), T.orderid)
WHERE P.type = 'visa';


same errror
0
 
8080_DiverCommented:
If all of the VarChar orderid's have some sort of trailing alpha characters then you should use something more like the following:

SELECT P.OrderID
FROM dbo.payments P
INNER JOIN dbo.tracking T
ON P.orderid like CONVERT(VarChar(50), T.orderid)
WHERE P.type = 'visa';

Open in new window

0
 
8080_DiverCommented:
Shazbot!

I wrote the ON clause backwards from what I was thinking!
SELECT P.OrderID
FROM dbo.payments P
INNER JOIN dbo.tracking T
ON T.orderid like CONVERT(VarChar(50), P.orderid)
WHERE P.type = 'visa';

Open in new window

0
 
ValentinoVBI ConsultantCommented:
8080_Diver: the conversion to varchar is indeed a good idea in this case.

However, I think one part of the requirement is currently missing: "that has an orderid in trackingnumbers table with more than one trackingnumber"
0
 
8080_DiverCommented:
ValentinoV, Ah, indeed!

SELECT P.OrderID
FROM dbo.payments P
INNER JOIN 
(
SELECT T.orderid, COUNT(orderid)
FROM dbo.tracking T
GROUP BY T.orderid
HAVING COUNT(orderid) > 1
) T
ON T.orderid like CONVERT(VarChar(50), P.orderid)
WHERE P.type = 'visa';

Open in new window

0
 
rgb192Author Commented:
with more than one trackingnumber... is missing

so orders more than one trackingnumber have more than one row with the same trackingnumbers.orderid, payments.orderid
0
 
ValentinoVBI ConsultantCommented:
It's no longer missing in that last query by 8080_Diver.  That's what the HAVING line is for.
0
 
rgb192Author Commented:
8080_Diver

No column was specified for column 2 of 'T'.
0
 
8080_DiverCommented:
rqb192,

So, give it a name. ;-)  That's the one that has the COUNT.
0
 
ValentinoVBI ConsultantCommented:
Or remove it from the SELECT.  If you don't need to retrieve it, there's no reason to have it in the SELECT list (the HAVING is sufficient for the "more than one" requirement).
0
 
rgb192Author Commented:
>>So, give it a name. ;-)  That's the one that has the COUNT.
>>Or remove it from the SELECT.  If you don't need to retrieve it, there's no reason to have it in the SELECT list (the HAVING is sufficient for the "more than one" requirement).

I dont understand how to change the query
0
 
rgb192Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.