SQL Query - merging to data sets.

xrapidx

Honorary Master
Joined
Feb 16, 2007
Messages
39,217
Ok, I'm having an off day at work, and can't figure this out. I need to join two tables, that have no key to join on.

TABLE A has a list of items, and their features. e.g.

ITEM_NAME, FEATURE_NAME, FEATURE_VALUE
Laptop1, RAM, 2GB
Laptop1, HDD, 160GB
Laptop1, Display, 14"
Laptop2, RAM, 1GB
Laptop2, Display, 11"
etc.

TABLE B is a list of features currently available, e.g.
FEATURE_NAME
RAM
HDD
Display
CPU
etc.

I need to join TABLE A to TABLE B so even if a feature (FEATURE_NAME) doesn't have a value (FEATURE_VALUE), it still gets outputed for each ITEM_NAME.

Anyone here know? Doing it in MSSQL....
 

icyrus

Executive Member
Joined
Oct 5, 2005
Messages
8,609
Left outer join?

Edit: I would guess something like this:

select * from table_a left outer join table_b on table_a.feature_name = table_b.feature_name

Not sure if there would be any MSSQL specific issues though.

Re-reading your question though, I think I may have misunderstood your requirements...
 
Last edited:

@udiS3

Senior Member
Joined
Feb 4, 2008
Messages
503
It sounds like you are over-complicating the query?
Table A feature name is one from the list available from Table b.

You want all Feature name and value if any from Table A?
select FEATURE_NAME, FEATURE_VALUE from Table_A

If you want to find those feature_name from Table B that are not listed by any item currently in Table a:
select * from table_b where feature_name not in (select feature_name from table_a)

Hope this helps....
 

xrapidx

Honorary Master
Joined
Feb 16, 2007
Messages
39,217
@@udiS3 : Neither of those will work. But thanks :)

--

I need to output a list that contains ALL features for an Item, even if they don't have a value set. TABLE A contains features for an item which are set, TABLE B contains a list f all features.
 

icyrus

Executive Member
Joined
Oct 5, 2005
Messages
8,609
I need to output a list that contains ALL features for an Item, even if they don't have a value set. TABLE A contains features for an item which are set, TABLE B contains a list f all features.

Ok so I think you want a right outer join then?

select * from table_a right outer join table_b on table_a.feature_name = table_b.feature_name

This seems like something that might be easier done in code rather than SQL though, or is that not an option?
 
Last edited:

xrapidx

Honorary Master
Joined
Feb 16, 2007
Messages
39,217
Ok so I think you want a right outer join then?

select * from table_a right outer join table_b on table_a.feature_name = table_b.feature_name

This seems like something that might be easier done in code rather than SQL though, or is that not an option?

I got it right eventually.

I created a tmp table by joining a distinct list of ITEM_NAME joined onto TABLE_B without using a join key, e.g. FULL OUTER JOIN ON 'x' = 'x'. Then joined that table back onto TABLE_A based on ITEM_NAME.

Out of interest - what code are you refering to? Application coding?
 

Surv0

Executive Member
Joined
Jan 7, 2006
Messages
5,723
ne1 know of a decent tutorial site where i can read up on outer joins etc?

Im a basic sql user and often need to use the outer join cmds but cant understand the logic, or am just blinded for some reason.
 

xrapidx

Honorary Master
Joined
Feb 16, 2007
Messages
39,217
Just an update - I had a decent drink at lunch - which brought my mind back into a decent state - what I was looking for was a CROSS JOIN.
 

xrapidx

Honorary Master
Joined
Feb 16, 2007
Messages
39,217
Quick question.

With a left or right join... is it the LEFT or RIGHT table that is used, or is it which ever is LEFT or RIGHT of the equal sign?

e.g.

table_a
LEFT JOIN
table b
ON table_b.id = table_a.id

Which table retains all its records?
 

ernstn

Expert Member
Joined
Jul 29, 2003
Messages
1,365
Quick question.

With a left or right join... is it the LEFT or RIGHT table that is used, or is it which ever is LEFT or RIGHT of the equal sign?

e.g.

table_a
LEFT JOIN
table b
ON table_b.id = table_a.id

Which table retains all its records?

table a
 
Top