Joining Two Tables (one with dates)

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

SQL Server 2005 Forums

Transact-SQL (2005)

Joining Two Tables (one with dates)

Author

Topic

rob196
Starting Member

9 Posts

Posted - 2009-05-22 : 13:24:51

Hi

Hopefully i can explain what i am trying to do.

Basically i have 2 tables, one has 2 columns Code and Description (call this Table A), the other table has multiple columns (Table B), but has a Code column that would link to table A.
Table B also has a Date column and for a specific date it can have only 1 Code (but multiple codes for one date) - i.e. i could have
Code: 1 Date 2009/05/05 and Code: 2 Date: 2009/05/05 then Code: 2 Date: 2009/05/06.

Now what i need to do is get all the items in Table B for a specified date (date is selected when a report is run), but i also need to include the Codes that where not included (i.e. the Codes from Table A that are not in table B for the specified date).
I have been able to get some of the codes there (only if they do not appear in Table B at all (i.e. for any date)).

So need to know how to handle this. I don't know how to "split" the data up by date so that i can get only the Codes that are not in B for a given Date (is this possible).

Thanks for any help in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-22 : 13:39:19

do you mean this?


SELECT a.*
FROM TableA a
LEFT JOIN  TableB b
ON b.Code=a.Code
AND b.Date=@Date
WHERE b.Code IS NULL

rob196
Starting Member

9 Posts

Posted - 2009-05-22 : 13:51:11

That would probably work, but the problem is the @Date as i am not sure how this would work, because i am using software that allows me to enter an SQL Statement then setup parameters (the one parameter is the Date) but not sure how to access this, it adds the parameter to the end of the SQL Statement.

Is there another way?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-22 : 13:56:08

whats the software that you're using for creation of this?

rob196
Starting Member

9 Posts

Posted - 2009-05-22 : 14:22:38

It is software that the company i work for created (it is not freely available), the one part is a report designer (like in VB).

If i add a date (would be the same as what is in Table B) to Table A - so that now Table A has Date, Code, Desc - this would now have multiple dates for a specific code and then do a lookup in Table B for items not in Table A where Dates are the same?

How would the above be done, if it is possible and would work?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-22 : 14:25:32

[code]SELECT a.*
FROM TableA a
LEFT JOIN TableB b
ON b.Code=a.Code
AND b.Date=a.Date
WHERE b.Code IS NULL
[/code]

rob196
Starting Member

9 Posts

Posted - 2009-05-22 : 14:27:13

Thanks will give it a try

Edit - Got it to work, thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-23 : 10:53:49

welcome