Logical Select statement

Transact-SQL
1

Hi all experts. Im just a newbie to SQL. I have a question on selecting my table.
I have two tables.

First Table:

ID	    DiscNo  TrackNo
--------------------------------------
JW099		0	0
JW099		1	1
JW099		2	1

Second Table:

ID	      DiscNo  TrackNo	Title
-------------------------------------------
JW099		1	1	Title One
JW099		1	2	Title Two
JW099		1	3	Title Three
JW099		1	4	Title Four
JW099		1	5	Title Five
JW099		1	6	Title Six
JW099		2	1	Title2 One
JW099		2	2	Title2 Two
JW099		2	3	Title2 Three
JW099		2	4	Title2 Four
JW099		2	5	Title2 Five
JW099		2	6	Title2 Six

The Logical condition in these is. If the First table has 0 DiscNo and 0 TrackNo it will get all the second table with the same ID. If not, it will only get the same DiscNo and TrackNo.

This is the expected result.

CatalogueID	DiscNo	TrackNo	Title
-------------------------------------------
JW099		1	1	Title One	-
JW099		1	2	Title Two	 |             
JW099		1	3	Title Three	 |        
JW099		1	4	Title Four	 |          
JW099		1	5	Title Five	 |	Result From First row on table 1
JW099		1	6	Title Six	 |         
JW099		2	1	Title2 One	 |
JW099		2	2	Title2 Two	 |
JW099		2	3	Title2 Three	 |
JW099		2	4	Title2 Four	 |
JW099		2	5	Title2 Five	 |
JW099		2	6	Title2 Six	-
JW099		1	1	Title One	-       Result from second row on first table
JW099		2	1	Title2 One	-	Result from third row on first table
  • I have made a simple Left Join. But im still lost. Thank you in advance experts.
2

Hello Jorge,

Please try as below:

create table #main (id varchar(20), discNo int, trackNo int)
create table #catalog (catalogID varchar(20), discNo int, trackNo int, title varchar(20))

insert into #main 
select 'JW099',	0,	0 union
select 'JW099',	1,	1 union
select 'JW099',	2,	1

insert into #catalog 
select 'JW099',	1,	1,	'Title One' union
select 'JW099',	1,	2,	'Title Two' union
select 'JW099',	1,	3,	'Title Three' union
select 'JW099',	1,	4,	'Title Four' union
select 'JW099',	1,	5,	'Title Five'union
select 'JW099',	1,	6,	'Title Six' union
select 'JW099',	2,	1,	'Title2 One' union
select 'JW099',	2,	2,	'Title2 Two' union
select 'JW099',	2,	3,	'Title2 Three' union
select 'JW099',	2,	4,	'Title2 Four' union
select 'JW099',	2,	5,	'Title2 Five' union
select 'JW099',	2,	6,	'Title2 Six'


select c.* from #main m right join 
#catalog c on 
(
(m.discNo = 0 and m.trackNo = 0 and m.id = c.catalogID)
or 
(m.discNo != 0 and m.trackNo != 0 and m.discNo = c.discNo and m.trackNo = c.trackNo )
)

drop table #main

drop table #catalog

I couldn't exactly get the order of the return result as what you wanted, you might need to find a way to order it correctly.

1 Like
3

Wow. My first try works and very clear. Thank you so much cyanfu. :smile: