I have a query that based 2 tables. I wrote a query with a left join on the base table but the result set returns multiple rows for each occurrence in the second table because of the left join. I want but to return all records  from on table A and only matching records from table B which   id but I would wan tit to keep return them vertically as the because it make it difficult to read when put in a spreadsheet . it want it to return the values horizontally so the rows are not increasing for each occurrence on table b.

Thanks in advance

Give us DDL and example data.

You can do so, like this:

DECLARE @parent TABLE (id int NOT NULL, vch_date datetime NOT NULL) 
INSERT INTO @parent (id, vch_date) VALUES (1, CAST(0x0000A30000000000 AS DateTime))

DECLARE @child2 TABLE (id int NOT NULL, f2 int NOT NULL)
INSERT INTO @child2 (id, f2) VALUES 
(1, 100),(1, 200),(1, 300)

Good day,

You question is not clear (to me at least), since if you have several rows in the seconf table that fit one row in the first table then you will get them all. This is how left join work.

If you post (1) queries to create the two tables that you have, and (2) queries to insert some sample data, and (3) the result that you want to get, then we will have the tools to understand and give a query  that fit your needs.

In the mean time please check this URL for a simple basic tutorial on JOINS:
http://www.w3schools.com/sql/sql_join.asp

See the following sample below:

declare @Parent TABLE (
	[id] [int] NOT NULL,
	[vch_date] [datetime] NOT NULL
) 

declare @child2 TABLE (
	[id] [int] NOT NULL,
	[f2] [int] NOT NULL
) 


declare @child1 TABLE (
	[id] [int] NOT NULL,
	[f1] [int] NOT NULL)


INSERT @Parent ([id], [vch_date]) VALUES (1, CAST(0x0000A30000000000 AS DateTime))

INSERT @child2 ([id], [f2]) VALUES (1, 100)
INSERT @child2 ([id], [f2]) VALUES (1, 200)
INSERT @child2([id], [f2]) VALUES (1, 300)

INSERT @child1 ([id], [f1]) VALUES (1, 100)
INSERT @child1([id], [f1]) VALUES (1, 200)



Select 
	parent.id,
	convert(date,parent.vch_date),
	q1.value as f1,
	q2.value as f2 
from 
(
	select row_number() over (order by id) [key], id,f1 as value from @child1
) as q1
right join 
(
	select row_number() over (order by id)  [key], id,f2 as value from @child2
) as q2
on 
	q2.[key]=q1.[key]
inner join 
	@Parent parent
on
	parent.id=q2.id

Result

Best Regards

Thank You Ricardo Lacerda. This example help a lot. I am very grateful.

Warmest Regards

Edward

Hi Edward,

I' am happy! Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

Thanks so much