asked
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
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.
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'
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...
This query returns the same results as your query so how is it not what you need?
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!
And thank you for that LEFT syntax recommendation...
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.
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 ) ;
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.
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
21 People are following this question.
