Hard Query! Need Help. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Hard Query! Need Help.

Author

Topic

mozo
Starting Member

5 Posts

Posted - 2010-07-22 : 09:34:21

Hi. I'm kinda new to sql and i reaaallyy could use some help right now.

Here are my tables
CREATE TABLE [dbo].[PRODUCT](
[id] [int] IDENTITY(1,1) NOT NULL,
[code] [nchar](10) NOT NULL,
[name] [nvarchar](50) NOT NULL,
[description_key] [varchar](50) NOT NULL,
[additional_info_key] [varchar](50) NOT NULL,
CONSTRAINT [PK_PRODUCT_1] PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[PRODUCT_HAS_COMPANY](
[id] [int] IDENTITY(1,1) NOT NULL,
[fk_product] [int] NOT NULL,
[fk_company] [tinyint] NOT NULL,
[fk_payment_info] [int] NULL,
CONSTRAINT [PK_PRODUCT_HAS_COMPANY] PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[COMPANY](
[id] [tinyint] IDENTITY(1,1) NOT NULL,
[company_gln] [char](14) NOT NULL,
CONSTRAINT [PK_COMPANY] PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[PRODUCT_HAS_COUNTRY](
[id] [int] IDENTITY(1,1) NOT NULL,
[fk_product] [int] NOT NULL,
[fk_country] [tinyint] NOT NULL,
[fk_payment_info] [int] NULL,
CONSTRAINT [PK_PRODUCT_HAS_COUNTRY] PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[COUNTRY](
[id] [tinyint] IDENTITY(1,1) NOT NULL,
[country_code] [varchar](10) NOT NULL,
CONSTRAINT [PK_COUNTRY] PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[PAYMENT_INFO](
[id] [int] IDENTITY(1,1) NOT NULL,
[method] [nvarchar](255) NOT NULL,
[fk_product] [int] NOT NULL,
CONSTRAINT [PK_PAYMENT_INFO] PRIMARY KEY CLUSTERED

I'm writing a procedure that takes country_code and company_gln as a parameters and i need to get data for product(id, code, name, ...) plus its payment method. but heres a catch.. payment method can be assigned to either a country or a company where company has priority. So far i was able to write something like this.

SELECT DISTINCT PRODUCT.ID, PRODUCT.CODE, PRODUCT.NAME, PAYMENT_INFO.METHOD , PRODUCT.DESCRIPTION_KEY, PRODUCT.ADDITIONAL_INFO_KEY
FROM PRODUCT
JOIN PRODUCT_HAS_COMPANY ON PRODUCT_HAS_COMPANY.FK_PRODUCT = PRODUCT.ID
JOIN COMPANY ON COMPANY.ID = PRODUCT_HAS_COMPANY.FK_COMPANY
JOIN PAYMENT_INFO ON PAYMENT_INFO.id = PRODUCT_HAS_COMPANY.fk_payment_info
WHERE (COMPANY.ECOD_COMPANY_GLN = @Ecod_company_gln )
UNION
SELECT DISTINCT PRODUCT.ID, PRODUCT.CODE, PRODUCT.NAME, PAYMENT_INFO.METHOD, PRODUCT.DESCRIPTION_KEY, PRODUCT.ADDITIONAL_INFO_KEY
FROM PRODUCT
JOIN PRODUCT_HAS_COUNTRY ON PRODUCT_HAS_COUNTRY.FK_PRODUCT = PRODUCT.ID
JOIN COUNTRY ON COUNTRY.ID = PRODUCT_HAS_COUNTRY.FK_COUNTRY
JOIN PAYMENT_INFO ON PAYMENT_INFO.id = PRODUCT_HAS_COUNTRY.fk_payment_info
WHERE (COUNTRY.ECOD_COUNTRY_CODE = @Ecod_country_code)

However this query as a result gives me available products with both payments for country and company..

Any feedback would be appreciated.

Kristen
Test

22859 Posts

Posted - 2010-07-22 : 09:48:38

Haven't read your schema, but in essence what you need is:


SELECT ...,
      COALESCE(PM2.ThePaymentMethod, PM1.ThePaymentMethod) AS ThePaymentMethod -- Company method, if a record exists, otherwise Generic method for product
FROM Products AS P
    JOIN PaymentMethod AS PM1
       ON PM1.ProductCode = P.ProductCode
      AND PM1.CompanyCode IS NULL -- Payment methods for ANY Company
    LEFT OUTER JOIN JOIN PaymentMethod AS PM2
       ON PM2.ProductCode = P.ProductCode
      AND PM2.CompanyCode = @MyCompanyCode

mozo
Starting Member

5 Posts

Posted - 2010-07-23 : 03:04:29

Thank you for the reply.

But I think its a little more complicated than that. PAYMENT_INFO table doesn't have foreign key of company and country and i have to get it from linking tables PRODUCT_HAS_COUNTRY and PRODUCT_HAS_COMPANY what gives me too many results because I'm using too many joins.

Is there a way to use COALESCE with the results from the query with UNION i posted before?

mozo
Starting Member

5 Posts

Posted - 2010-07-23 : 03:47:52

Ok I kinda figured it out.

ATM i have something like this

SELECT DISTINCT PRODUCT.ID, PRODUCT.CODE, PRODUCT.NAME, COALESCE(P1.METHOD, P2.METHOD) , PRODUCT.DESCRIPTION_KEY, PRODUCT.ADDITIONAL_INFO_KEY
FROM PRODUCT
LEFT JOIN PAYMENT_INFO P1 ON P1.fk_product=PRODUCT.id AND P1.id IN(
SELECT fk_payment_info FROM PRODUCT_HAS_COMPANY
WHERE PRODUCT_HAS_COMPANY.fk_company =
(SELECT id FROM COMPANY WHERE COMPANY.company_gln=@company_gln)
)
LEFT JOIN PAYMENT_INFO P2 ON P2.fk_product=PRODUCT.id AND P2.id IN(
SELECT fk_payment_info FROM PRODUCT_HAS_COUNTRY
WHERE PRODUCT_HAS_COUNTRY.fk_country =
(SELECT id FROM COUNTRY WHERE COUNTRY.country_code=@country_code)
)

..and the results I'm getting are almost good but I'm also getting products with nulls as payment methods. So my question now is how to not display a row if there is a null in one of the values?

Kristen
Test

22859 Posts

Posted - 2010-07-23 : 03:57:26

"Is there a way to use COALESCE with the results from the query with UNION i posted before?"

No, sorry, that was my point - should have explained it more clearly. You need to OUTER JOIN the various tables for "possible" data, and then COALESCE them in the relevant order to get the "most relevant" value.

Your nested IN statements will be very inefficient. Use JOIN if the current IN statement will only return one matching row, otherwise use EXISTS instead

"I'm getting are almost good but I'm also getting products with nulls as payment methods. So my question now is how to not display a row if there is a null in one of the values?"

If I have understood it is OK if P1.METHOD is NULL provided that P2.METHOD is NOT NULL, in which case you want to excldue records where both columns are NULL, i.e.

WHERE (P1.METHOD IS NOT NULL OR P2.METHOD IS NOT NULL)

mozo
Starting Member

5 Posts

Posted - 2010-07-23 : 04:33:46

Thanks a lot again. Got it to return the results i need. (I should have figured out the answer to the second question by myself.. it wasn't that hard:P)

Right now I'm trying to make it more efficient as u said, i replaced nested selects and the problem is I don't know the syntax for nested joins to replace INs.

SELECT DISTINCT PRODUCT.ID, PRODUCT.CODE, PRODUCT.NAME, COALESCE(P1.METHOD, P2.METHOD) , PRODUCT.DESCRIPTION_KEY, PRODUCT.ADDITIONAL_INFO_KEY
FROM PRODUCT
LEFT JOIN PAYMENT_INFO P1 ON P1.fk_product=PRODUCT.id AND P1.id IN(
SELECT fk_payment_info FROM PRODUCT_HAS_COMPANY
JOIN COMPANY ON COMPANY.id=PRODUCT_HAS_COMPANY.fk_company AND COMPANY.ecod_company_gln=@Ecod_company_gln
)
LEFT JOIN PAYMENT_INFO P2 ON P2.fk_product=PRODUCT.id AND P2.id IN(
SELECT fk_payment_info FROM PRODUCT_HAS_COUNTRY
JOIN COUNTRY ON COUNTRY.id=PRODUCT_HAS_COUNTRY.fk_country AND COUNTRY.ecod_country_code=@Ecod_country_code
)WHERE (P1.METHOD IS NOT NULL OR P2.METHOD IS NOT NULL)

In this situation INs will return >1 rows. Is there a way to replace INs with JOINs?

Kristen
Test

22859 Posts

Posted - 2010-07-23 : 11:54:45

"In this situation INs will return >1 rows. Is there a way to replace INs with JOINs?"

No, it will select rows from the other tables multiple times - once for each row in the child-JOIN (which is not what you want).

So exists would be better than IN (the IN list will be long, and you are only needing to match one value, so EXISTS will terminate as soon as it finds ONE value whereas the IN statment has to find ALL the possible values ...)

Syntax for nested JOIN would be:


FROM MyTableA AS A
   LEFT OUTER JOIN
   (
        TableWhereRowMightExist AS B
        JOIN TableWhereRowMustExistIfTableBHasARow AS C
            ON C.ID = B.ID
   )
      ON B.ID = A.ID

and the EXISTS syntax would be something like:


    LEFT JOIN PAYMENT_INFO P1 
        ON P1.fk_product=PRODUCT.id
       AND EXISTS (SELECT *
                   FROM PRODUCT_HAS_COMPANY 
                        JOIN COMPANY 
                            ON COMPANY.id=PRODUCT_HAS_COMPANY.fk_company 
                           AND COMPANY.ecod_company_gln=@Ecod_company_gln
                   WHERE fk_payment_info = P1.id
                   )

mozo
Starting Member

5 Posts

Posted - 2010-07-26 : 04:03:11

The results of the refactoring..

The one with EXISTS:

SELECT DISTINCT PRODUCT.id, PRODUCT.code, PRODUCT.name, COALESCE(P1.method, P2.method) AS method, PRODUCT.description_key, PRODUCT.additional_info_key
FROM PRODUCT
LEFT JOIN PAYMENT_INFO P1 ON P1.fk_product=PRODUCT.id AND EXISTS(
SELECT fk_payment_info FROM PRODUCT_HAS_COMPANY
JOIN COMPANY ON COMPANY.id=PRODUCT_HAS_COMPANY.fk_company AND COMPANY.ecod_company_gln=@Ecod_company_gln
WHERE fk_payment_info = P1.id
)
LEFT JOIN PAYMENT_INFO P2 ON P2.fk_product=PRODUCT.id AND EXISTS(
SELECT fk_payment_info FROM PRODUCT_HAS_COUNTRY
JOIN COUNTRY ON COUNTRY.id=PRODUCT_HAS_COUNTRY.fk_country AND COUNTRY.ecod_country_code=@Ecod_country_code
WHERE fk_payment_info=P2.id
)WHERE (P1.METHOD IS NOT NULL OR P2.METHOD IS NOT NULL)

And the one with nested JOINs which I'm particularly proud of:P

SELECT DISTINCT PRODUCT.id, PRODUCT.code, PRODUCT.name, COALESCE(P1.method, P2.method) AS method, PRODUCT.description_key, PRODUCT.additional_info_key
FROM PRODUCT
LEFT JOIN (PAYMENT_INFO P1
JOIN (PRODUCT_HAS_COMPANY
JOIN COMPANY ON COMPANY.id=PRODUCT_HAS_COMPANY.fk_company AND COMPANY.ecod_company_gln=@Ecod_company_gln
)
ON PRODUCT_HAS_COMPANY.fk_payment_info = P1.id
)
ON P1.fk_product=PRODUCT.id
LEFT JOIN (PAYMENT_INFO P2
JOIN (PRODUCT_HAS_COUNTRY
JOIN COUNTRY ON COUNTRY.id=PRODUCT_HAS_COUNTRY.fk_country AND COUNTRY.ecod_country_code=@Ecod_country_code
)
ON PRODUCT_HAS_COUNTRY.fk_payment_info=P2.id
)
ON P2.fk_product=PRODUCT.id
WHERE (P1.METHOD IS NOT NULL OR P2.METHOD IS NOT NULL)

both queries return the same results.

All thanks to Kristen. Can't think of the words that would express my graditute.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources