If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > Help with SQL statement - complex join of 2 tables

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 02-25-04, 21:13
sly001 sly001 is offline
Registered User
 
Join Date: Feb 2004
Posts: 1
Help with SQL statement - complex join of 2 tables

Consider 2 tables, tblColors and tblMatching.

tblColors contains 2 fields: id_color, name_color.
id_color is Primary Key
Table is populated with data:
1 red
2 white
3 blue
4 green
5 yellow
6 black

tblMatching contains 3 fields: id_match, idcolor1_match, idcolor2_match.
id_match is primary key.
idcolor1_match and id_color2_match are both foreign keys pointing to tblColors.id_color.
This table lists matching colors, like:
id_match.....idcolor1_match.....idcolor2_match
1..................1........................4 (for red-green)
2..................2........................3 (for white-blue)
3..................3........................1 (for blue-red)
4..................1........................2 (for red-white)
5..................4........................5 (for green-yellow)

I need a SELECT QUERY (JOIN) that will display all the colors in tblColors that do NOT match a specific color in tblMatching. With the sample data above, I would need a "list" of all colors NOT matched to red. In otherwords, show all colors in tblColors where they are not matched to RED in tblMatching. In this case, the result set should diaplay Yellow and Black. (Since I am searching based on Red, the results should not display Red).

Searching on Red, so don't display Red.
Red matches Green at id_match 1, so don't display Green.
Red matches Blue at id_match 3, so don't display Blue.
Red matches White at id_natch 4, so don't display White.
Therefore, display only Yellow and Black.

Any ideas for this JOIN statement?
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On