Joining two queries

Question

Joining two queries

Viewing 7 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

neb2886 Valued Member Points: 73 More actions · Answer 1

I am working on getting those tables together if this one doesn't work, that will take me some time. Thanks for bearing with me.

That query looks like what I need, however when I ran it I received "Incorrect syntax near 'VARCHAR'."

Jared SSC Guru Points: 61793 More actions · Answer 2

Oops... Change the cte query to this:

;WITH tagTypes (tagType)

AS

(Select '%-FRN%'

UNION

Select '%-BCK%'

UNION

Select '%-PPP%'

UNION

Select '%-MES%'

UNION

Select '%-MMT%')

CTEs cannot have the data type declared, it is implicit.

Jared
CE - Microsoft

neb2886 Valued Member Points: 73 More actions · Answer 3

Thanks for this. It still only returned the one row. I will work on putting the data/table structures together and post it up here.

Jared SSC Guru Points: 61793 More actions · Answer 4

You probably need to change all of your INNER JOINs to LEFT JOIN

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 5

Look at this code:

CREATE TABLE #identity_map (id int identity(1,1), name varchar(110));

INSERT INTO #identity_map(name)

SELECT 'sql-mmt';

;WITH tagTypes (tagType)

AS

(Select '%-FRN%'

UNION

Select '%-BCK%'

UNION

Select '%-PPP%'

UNION

Select '%-MES%'

UNION

Select '%-MMT%')

SELECT *

FROM tagTypes

LEFT JOIN #identity_map IM

ON IM.name LIKE tagTypes.tagType

It runs and gives the data like you want. However, if you INNER JOIN on any of the NULL values, it will get rid of that row. So, all of your joins in this case have to be LEFT JOINs.

Jared
CE - Microsoft

neb2886 Valued Member Points: 73 More actions · Answer 6

I changed them all to left joins and still nothing.

Can't see why this wouldn't be working, it follows the same logic of what you just posted.

Jared SSC Guru Points: 61793 More actions · Answer 7

If I were in your position, I would start with only the first 2 tables. Then add the 3rd and make sure it is giving you desired results. Then the 4th... etc. You have to find where the problem lies, and you can easily do it in the way I just described; i.e. if the first join with only the CTE (tagTypes) and IM, then you know something is wrong with there.

Jared
CE - Microsoft