Left join on two columns? - 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

Left join on two columns?

Author

Topic

kpaske
Starting Member

3 Posts

Posted - 2010-03-15 : 16:25:32

I'm trying to do a join on a lookup table, where the rows I'm joining on could be in one of two columns. In other words, the right table has a value that could be in one of two specific text formats (i.e. YYMM or YYMMDD), and the values to match on are in two different columns in the right table. To make it more complicated, I also need to display the rows if the value is NULL, I only want to display the lowest matching date if the value is in the second column, and I don't want to duplicate any rows. Confusing enough?

Well, I have a query that mostly works, but I can't figure out how to make it not repeat the values for "Name" twice (once for each select statement in the union):


    SELECT
        Name,
	CalendarDate
    FROM
        ComponentBase CB LEFT JOIN WorkWeek WW ON PODate = WW.YYwwWWD
    UNION
    SELECT
        Name,
	Min(CalendarDate)
    FROM
        ComponentBase CB LEFT JOIN WorkWeek WW ON PODate = WW.YYwwWW
	GROUP BY ComponentName

How can I rewrite this code so that it only shows either NULL, or a single result from whatever column the correct value is found in?

Thanks in advance!

kpaske
Starting Member

3 Posts

Posted - 2010-03-15 : 16:50:02

Actually, I should be more specific as this query is really just a proof of concept for the real view I'm trying to build. I need to be able to select the correct date from the lookup table where the value could either be NULL, or in the YYwwWW or YYwwWWD format. The lookup table looks like this:


ID	CalendarDate		YY	WW	D	YYwwWWD		YYwwWW	WWStartDate		DOW
41	2009-01-01 00:00:00.000	09	01	4	09ww01.4	09ww01	2008-12-29 00:00:00.000	Thursday
42	2009-01-02 00:00:00.000	09	01	5	09ww01.5	09ww01	2008-12-29 00:00:00.000	Friday
43	2009-01-03 00:00:00.000	09	01	6	09ww01.6	09ww01	2008-12-29 00:00:00.000	Saturday
44	2009-01-04 00:00:00.000	09	01	7	09ww01.7	09ww01	2008-12-29 00:00:00.000	Sunday
45	2009-01-05 00:00:00.000	09	02	1	09ww02.1	09ww02	2009-01-05 00:00:00.000	Monday
46	2009-01-06 00:00:00.000	09	02	2	09ww02.2	09ww02	2009-01-05 00:00:00.000	Tuesday
47	2009-01-07 00:00:00.000	09	02	3	09ww02.3	09ww02	2009-01-05 00:00:00.000	Wednesday
48	2009-01-08 00:00:00.000	09	02	4	09ww02.4	09ww02	2009-01-05 00:00:00.000	Thursday

Any help would be appreciated!

ray-SQL
Starting Member

18 Posts

Posted - 2010-03-15 : 18:30:50

How about this


SELECT
        CB.Name,
	WW.CalendarDate,
	(select Min(CalendarDate) FROM
        WorkWeek W2 where w2.yywwww=WW.yywwww
        group by w2.yywwww) 
    FROM
        ComponentBase CB LEFT JOIN WorkWeek WW ON PODate = WW.YYwwWWD

It is not pretty and the performance might be bad if the dataset is big, but it should work.

Ray Dai

kpaske
Starting Member

3 Posts

Posted - 2010-03-15 : 19:16:42

Thanks for the suggestion, Ray. That query didn't quite get me what I was looking for - it only returned values when the date was in the YYwwWWD format, and it returned dates in two different columns. What I wanted was to return only a single date in one column, and have it determine which column to pull the date from depending on the format (which will be validated in the frontend app).

I figured out a solution. It's probably not the most efficient way, but it works for now. I'd love to make it better if there is a better way, but this is what i did:


    SELECT
        Name,
	CalendarDate =  CASE LEN(PODate) 
			WHEN 6 THEN Min(WW1.CalendarDate)
			WHEN 8 THEN Min(WW2.CalendarDate)
			ELSE NULL
		        END
    FROM
        ComponentBase CB LEFT JOIN WorkWeek WW1 ON PODate = WW1.YYwwWW
			 LEFT JOIN WorkWeek WW2 ON PODate = WW2.YYwwWWD
    GROUP BY ComponentName, PODate

Any suggestions on how to make this more efficient would be appreciated because I actually have to do this on two date fields in my view, creating four joins! It'll probably be fine for small tables but that doesn't seem very scalable.

ray-SQL
Starting Member

18 Posts

Posted - 2010-03-16 : 10:12:40

You are welcome.
Instead of

CalendarDate =  CASE LEN(PODate) 
			WHEN 6 THEN Min(WW1.CalendarDate)
			WHEN 8 THEN Min(WW2.CalendarDate)
			ELSE NULL
		        END

maybe you can try

CalendarDate =  COALESCE(Min(WW1.CalendarDate), Min(WW2.CalendarDate))

FYI, your sample data helped, but if there is more complete code/data, that helps other posters to take your code and run it immediately and that could help in the future.

Please see the pinned FAQ on top of this forum on how to post more information. Or:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Ray Dai

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources