SQL Query - merging to data sets.

xrapidx

xrapidx

Honorary Master
Joined
Feb 16, 2007
Messages
42,187
Reaction score
4,038
Location
Cape Town
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....
 
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:
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....
 
@@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.
 
xrapidx said:
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.
Click to expand...

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:
icyrus said:
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?
Click to expand...

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?
 
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.
 
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.
 
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?
 
xrapidx said:
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?
Click to expand...

table a
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X