x

SELECT should always return all rows from one table.

Hello,

I do appreciate your help. This probably has a simple solution but I can not get through it...

I have 2 tables (ControlContent and SelectedValues) and I need to create a SELECT that returns 2 columns in the result.

In this table is list of all available cars, bikes etc.

 ControlContent
 CCID   ControlID   Name
 1      Car         Ford
 2      Car         Ferrari
 3      Car         Fiat
 4      Car         Chrysler
 5      Bike        Harley
 6      Bike        Honda
 7      Bike        Suzuki
 8      Bike        Yamaha


In this table is index of what user selected.

 SelectedValues
 SVID   CCID        OrderID
 1      1           2146
 2      3           2146
 3      2           2147
 4      3           2147


This is the required result

The SELECT will have 2 parameters, for example {par1}='Car' and {par2}=2146. Car means you want to get all the cars and 2146 is ID of order.

 Name       Value
 Ford       1
 Ferrari    0
 Fiat       1
 Chrysler   0

I need to always return ALL ROWS from table ControlContent with a given ControlID in one column; all types of cars for example. And in the second column I need to know if car is selected. 1=selected, 0=unselected. This result fills a table in TableControl in a windows form, so the user always see all types of cars, and has has the option to select/unselect them on a certain order. In another window the user can see all types of bikes etc.

This is my latest attempt at a select that DOES NOT WORK.

 SELECT  ControlContent.Name, CASE WHEN CC_ID IS NULL THEN 0 ELSE 1 END AS Value
 FROM    SelectedValues
         RIGHT OUTER JOIN ControlContent ON SelectedValues.SpecialniInformaceID = dbo.SpecialniInformace.SpecialniInformaceID
 WHERE   ( ControlContent.ControlID = {par1} )
         AND ( SelectedValues.OrderID = {par2}
               OR SelectedValues.OrderID IS NULL
             )


Thank you in advance for any help.

Petr

more ▼

asked Apr 22, 2016 at 10:23 AM in Default

avatar image

petr.pesava_kartonie_cz
31 3

You have multiple good answers below. For each answer that is helpful, click on the thumbs up next to those answers. If one answer lead to a solution, mark that answer by clicking on the check mark next to it.

Apr 28, 2016 at 09:21 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

I would resist the temptation to use RIGHT joins--similar to Tom's answer. Your code will be more readable as more tables are added to your queries.

 SELECT cc.Name, CASE WHEN sv.CCID IS NULL THEN 0 ELSE 1 END [Value]
 FROM dbo.ControlContent cc  
 LEFT JOIN dbo.SelectedValues sv ON (sv.CCID = cc.CCID AND sv.OrderID = 2146)
 WHERE cc.ControlID = 'Car'
more ▼

answered Apr 24, 2016 at 07:10 PM

avatar image

Scot Hauder
6.5k 13 16 22

You might be right, but I do not care at this moment about LEFT RIGHT. Point is that results of this SELECT are not what I need...

Apr 25, 2016 at 06:47 AM petr.pesava_kartonie_cz

This query returns the same results as your query so how is it not what you need?

Apr 25, 2016 at 08:52 AM Scot Hauder

Sorry Scot. My fault. I did not check your query, I thought you just recommended Tom's approach which did not work for me. Your query is great and definitely more elegant solution than my query with groups and sum!

I am not used to use this kind of joins, I should start.

Thank you!

Apr 25, 2016 at 09:31 AM petr.pesava_kartonie_cz

And thank you for that LEFT syntax recommendation...

Apr 25, 2016 at 10:04 AM petr.pesava_kartonie_cz
(comments are locked)
10|1200 characters needed characters left

It looks like the problem is in your join. You are joining 2 tables but referencing a column from a third table? I also don't think you are joining on the right column, based on your tables.

Try this:

 SELECT c.Name
     , Value = CASE WHEN CC_ID is null THEN 0 ELSE 1 END
 FROM ControlContent cc
     LEFT JOIN SelectedValues sv
         ON sv.CCID = cc.CCID
 WHERE cc.ControlID = {par1}
     AND
     (
         sv.OrderID = {par2}
         OR sv.OrderID is null
     )
 ;
more ▼

answered Apr 22, 2016 at 02:51 PM

avatar image

Tom Staab ♦
14.5k 7 14 21

(comments are locked)
10|1200 characters needed characters left

Here is a link to help you better understand joins:

https://www.mssqltips.com/sqlservertip/1667/sql-server-join-example/

I'm sure that this will help you to understand what you are trying to do.

more ▼

answered Apr 22, 2016 at 12:48 PM

avatar image

sjimmo
1.6k 1 3 5

(comments are locked)
10|1200 characters needed characters left

Hello and thank you.

I was working on it long time and as I was tired I made a mistake and I copied messed uncomplete SELECT and I did not see this mistake when I was checking my question. I am really sorry about that!

There is of course no third table... Select should be as below. According to me it is almost the same as Tom Stabb wrote, I only use RIGHT JOIN.

 SELECT  ControlContent.Name, CASE WHEN CC_ID IS NULL THEN 0 ELSE 1 END AS Value
  FROM    SelectedValues
          RIGHT OUTER JOIN ControlContent ON SelectedValues.CCID = dbo.ControlContent.CCID
  WHERE   ( ControlContent.ControlID = {par1} )
          AND ( SelectedValues.OrderID = {par2}
                OR SelectedValues.OrderID IS NULL
              )

This gives me result like that:

  Name       Value
  Ford       1
  Fiat       1
  Chrysler   0

Ferrari is missing because it is on order 2147 which means column SelectedValues.OrderID is not NULL and is not 2146 and this is point where I am out of ideas.

more ▼

answered Apr 22, 2016 at 06:25 PM

avatar image

petr.pesava_kartonie_cz
31 3

(comments are locked)
10|1200 characters needed characters left

Hello, so I have something that looks like it works. Of course I am open to any remarks.

 SELECT        dbo.ControlContent.Name, SUM(CASE WHEN dbo.SelectedValues.OrderID = 2146 THEN 1 ELSE 0 END) AS Value
 FROM            dbo.SelectedValues RIGHT OUTER JOIN
                          dbo.ControlContent ON dbo.SelectedValues.CCID = dbo.ControlContent.CCID
 WHERE        (dbo.ControlContent.ControlID = 'Car')
 GROUP BY dbo.ControlContent.Name
more ▼

answered Apr 24, 2016 at 08:54 AM

avatar image

petr.pesava_kartonie_cz
31 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x162
x159

asked: Apr 22, 2016 at 10:23 AM

Seen: 92 times

Last Updated: Apr 28, 2016 at 09:21 AM

Copyright 2017 Redgate Software. Privacy Policy