April 1, 2010 at 1:38 pm
thanks in advance.
setup:
two tables:
1 is full detail records of inventory items.
2 is history records of work done on items in table 1(can be many records here to a single item, or no records).
table 1 has an identity key field (InvID). table 2 uses this to join to table 1.
i need all records from table 1 to show and add a column with an indicator (like '*') when history items are present in table 2.
i can not seem to get a clean return from my attempts. please help.
D
April 1, 2010 at 1:43 pm
Can you post what you've tried?
This appears to be a homework or study question. And most of us here DONT have a problem helping, we generally don't want to do your work for you.. 😀
Also, this is useful info....
http://www.sqlservercentral.com/articles/Best+Practices/61537/
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 2, 2010 at 5:59 am
DECLARE @Table1
TABLE (
row_id INTEGER PRIMARY KEY
);
DECLARE @Table2
TABLE (
row_id INTEGER PRIMARY KEY
);
INSERT @Table1 (row_id) VALUES (1);
INSERT @Table1 (row_id) VALUES (2);
INSERT @Table1 (row_id) VALUES (3);
INSERT @Table2 (row_id) VALUES (1);
INSERT @Table2 (row_id) VALUES (3);
SELECT T1.row_id,
indictor =
(
SELECT '*'
FROM @Table2 T2
WHERE T2.row_id = T1.row_id
)
FROM @Table1 T1;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 2, 2010 at 8:04 am
thanks for the replies.
Jason,
I can only tell you this is not homework or study, this is for a project i became the owner of.
I tried using the count(*) but this returned a value and when it joined tables i got two rows returned. one row with the count value and one with 0.
a coworker insisted i needed to use a CASE statement to put the * there if the count was > 0. i could not figure this out.
i poked and tried for a week to get something working with clean results. unfortunately i did not save anything if it did not work right.
i searched the web and here before posting.
Paul,
i got an error from this solution because the values in table 2 can have many records and it did not like that.
ie:
INSERT @Table2 (row_id) VALUES (1);
INSERT @Table2 (row_id) VALUES (3);
INSERT @Table2 (row_id) VALUES (3);
INSERT @Table2 (row_id) VALUES (3);
returned...
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
so i tossed in a subquery with distinct
select '*'
from
(
select distinct T3.row_id
from @table2 T3) T2
where T1.row_id = T2.row_id)
this works and i get the results i expect.
i know asking if this is the best way to code is very subjective, but is there anything that should be done different?
April 2, 2010 at 8:13 am
I had to guess based on very little detail in the first post.
DECLARE @Table1
TABLE (
row_id INTEGER NOT NULL
);
DECLARE @Table2
TABLE (
row_id INTEGER NOT NULL
);
INSERT @Table1 (row_id) VALUES (1);
INSERT @Table1 (row_id) VALUES (2);
INSERT @Table1 (row_id) VALUES (3);
INSERT @Table2 (row_id) VALUES (1);
INSERT @Table2 (row_id) VALUES (3);
INSERT @Table2 (row_id) VALUES (3);
INSERT @Table2 (row_id) VALUES (3);
SELECT T1.row_id,
indictor =
(
SELECT TOP (1)
'*'
FROM @Table2 T2
WHERE T2.row_id = T1.row_id
)
FROM @Table1 T1;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply