Comparing data in multiple columns/rows in same table with query

[j1s15n]

I have a table that has 2 columns that I need the Database to match. I have a bunch of people that have listed what they have in one column and in the next column, they list what they want. I need the Database to tell me which ones match.

Johnny has an apple but wants an orange
Sally has an orange but wants an apple
Derek has a pear, but wants a banana.

In the above scenario, the Database would tell me that the matches are Johnny and Sally because they both have what each other wants. Derek would not show up since he didn't have a person that wanted a pear. Poor Derek :-(

Table name: FODEIDE18 Exchange Requests
Columns:
ID
ROM
Rank
Name
Current School Selection
Exchange School Request
Priority
DT Functional Area

I need the DB to compare Current School Selection and Exchange School Request

Any assistance you can provide would be amazing!

[Luuk]

Why not name this columns, and refer to them like this "I have a bunch of people that have listed what they have in one column and in the next column, they list what they want."?

when you have a table ('Table1') with these fields:

Name
HaveThis
WantThis
 

With the following data:

INSERT INTO Table1(Name, HaveThis, WantThis) Values ('Johnny', 'Apple', 'Orange')
INSERT INTO Table1(Name, HaveThis, WantThis) Values ('Sally', 'Orange', 'Apple')
INSERT INTO Table1(Name, HaveThis, WantThis) Values ('Derek', 'Pear', 'Banana')
 

You could write your query as:

SELECT t1.Name, t1.HaveThis, t1.WantThis, t2.Name
FROM Table1 t1
INNER JOIN Table1 t2 on t2.WantThis=t1.HaveThis and T2.HaveThis=t1.WantThis;
 

And because Derek is a loozer:

SELECT t1.[Id], t1.[Name], t1.[HaveThis], t1.[WantThis]
FROM Tabel1 AS t1 
WHERE Name not IN (
  SELECT t1.[Name]
  FROM Tabel1 AS t1 
  INNER JOIN Tabel1 AS t2 ON (t2.WantThis=t1.HaveThis) AND (t2.HaveThis=t1.WantThis));
 

[PhilOfWalton]

I don't think it's as simple as that.
What happens when Phil also has an orange but wants an apple? So both Sally & Phil match Johnny.

As an aside, try to avoid spaces in Object Names (Tables, Queries, Forms & Reports) and also in field names. Also ID as a field name tells you nothing about the field - RequestID might be better. Also, if you want avoid writers' cramp, keep names short but meaningful. What is wrong with "Owned" and "Requested"?

Phil

[Luuk]

@PhilWalton: you are right, this was just a quick answer to get j1s15n started, now let's wait for j1s15n

[NeoPa]

That sort of design is such a bad idea. It seems like a quick read of Database Normalisation and Table Structures might help your progress.

While it's possible to check for similar data across multiple columns, most experienced database designers will warn you against such a design. The more you try to accomplish the harder it gets.

[cameronhunter]

Seems like it's already solved by an expert.

[NeoPa]

CameronHunter:
Seems like it's already solved by an expert.

To whom would that be?

It doesn't seem solved to me at all. Luuk has done well as far as it goes, but the only real gain here is by learning how and why to do it a completely different way.

[Luuk]

NeoPa:… is by learning how and why to do it a completely different way

It seems this query is about learning in an already existing situation. (why otherways a tablename like 'FODEIDE18'?)

Therefore:
- Yes proper database design is 'good'!
- but if you already have a system, or something, build why change everything to just have a 'proper database design'?.

I know a good design might help in the end, but sometimes a small modification to existing crap, might be worth fiddling around with.... 😊

[NeoPa]

It's hard to be absolute with such a statement of course Luuk, but in this case I would be comfortable saying that such assistance would do the OP no favours.

If what they take away from this is that you can get by even with such a totally inappropriate design then they've lost and we've let ourselves down. As a general rule we aim to educate rather than get people past their current sticking point. If you can do the latter without compromise of the former then fine, but never compromise good understanding for the sake of a quick fix. That's not helpful for anyone.

Although I speak as a site administrator, I'm confident that almost all the moderators and experts view this in a similar way.

[NeoPa]

Let me just add that, while I'm disagreeing with the idea you put forward about the advisability of 'fiddling around with crap', I am by no means complaining or criticising your involvement in this thread. Again it's hard to be absolute but it's certainly a murky area at least.

Even if I may view your efforts as misdirected, I still, as do others I'm sure, see them as valuable and offered in good heart.

If I can offer guidance as how best to go forward from here then I'm happy.

[Luuk]

@NeoPa: OK. I understand that you see educating as part of the service given here.

I am also under the impression that even quick fix can teach someone. but unfortunatly some (not all!) posters are here for the quick answer, and not for the education.

Now, let's wait if j1s15n comes back, and askes a smart question about above converstation 😉😉

[NeoPa]

Absolutely Luuk.

When I was learning database design, which was quite a paradigm-shift from my old development understanding, it was a very important turning point for me when it was explained to me that my older way of thinking was not appropriate for databases. I had to be shaken up in order to treat it as an issue as big as it was and not just another step along the same path.

Once I'd made that change though, the importance became unavoidable. Trying to work with databases without such an understanding, even for otherwise good developers (especially for them in a very real way), is like working with one hand tied behind your back. It's also a bit like wading into a swamp. The longer you push on before turning back and using the bridge, the deeper you get bogged down.

It's never fun to be told to start again because you're doing it all wrong, but it seems the kinder approach to me.

[j1s15n]

Unfortunately, the fields are set because I pull them from a website where individuals put in requests to exchange schools. I export the excel from the website, then import into the DB.

@Luuk

[j1s15n]

Thanks for all your input! Yes, I am an extreme amateur when it comes to databases. I learn by doing and using examples people post via different threads. Unfortunately, being in the profession I'm in, I don't have a bunch of time to really get educated like I would love to do.

Regarding the fields, I can't change them because I pull the data from a website that a 3rd party runs for us. Here's what it's used for:

We select over 1K people to attend different schools around the world. This is the first year we have given the opportunity to the customer to ask for the option to exchange with another school. Every person gets 2 exchange requests and must prioritize them 1,2 respectively.

We extract the information and then must try to decipher the information, which, at face value seems really easy. Until. Until we start looking at multiple options to help multiple people. Think NFL where multiple teams agree to move around players and draft picks in order to complete 1 big trade.

In the end, if a person currently is going to Alabama, but requests a school in the National Capitol Region, and also another school in New Mexico, I'm hoping the database will, in the end tell me all the people who currently have the schools that the person requested.

It's complicated as heck for me, and platforms such as this, really help a ton, especially when people help out with examples so I can try multiple things to see if they work or not.

I updated the SQL to state:

SELECT *
FROM   [FODEIDE18 Exchange Requests]
       LEFT JOIN
       [FODEIDE18 Exchange Requests1]
  ON   ([FODEIDE18 Exchange Requests].[Current School Selection]=[FODEIDE18 Exchange Requests1].[Exchange School Request])
 AND   ([FODEIDE18 Exchange Requests].[Exchange School Request]=[FODEIDE18 Exchange Requests1].[Current School Selection])

This gives a 70% solution... I still have to eliminate the blank entries manually, and still have to do a hand match, but proves to be so much less time consuming!

Thank you all for helping an uneducated guy such as myself!

[NeoPa]

Hi J1s15n.

Let me first say that such an explanation will give you a certain leeway. Primarily we want to be helpful. Some of us can sound grumpy at times but that's the real reason we're here. Sure we don't like our time to be wasted, and as an administrator that's one of my main responsibilities - protecting our experts from members who misbehave, but I'm also very keen on education rather than simply getting people past individual hurdles. That can be like pushing a door with clutter behind it. The further you get the door open the harder it is to continue.

If we know you won't suffer unduly by being helped past a hump then that's something we can do (and Luuk has) as long as we make it clear for all readers where we're at, and why.

One comment I'd make is about your SQL (and it may turn into more) :

1. You may need to specify :
   

   SELECT [FODEIDE18 Exchange Requests].*

   as a simple * on its own is ambiguous (and SQL doesn't like that).
2. Where possible avoid names with spaces as well as names that are longer than they need to be. They just make your work harder. I suspect you're working with what you have so no scope there but it's always helpful to understand.
3. You may want to use ALIASes for the tables to make the SQL easier to read and work with :
   

   SELECT [t1].*
   FROM   [FODEIDE18 Exchange Requests] AS [t1]
          LEFT JOIN
          [FODEIDE18 Exchange Requests1] AS [t2]
     ON   ([t1].[Current School Selection]=[t2].[Exchange School Request])
    AND   ([t1].[Exchange School Request]=[t2].[Current School Selection])

ALIAS is often written simply as AS, and also sometimes omitted completely.

[j1s15n]

OHHHHHH That’s what AS is! I’ve tried using it to rename a table in sql, but never new it was a real thing.
I really appreciate you taking the time to respond. I spent a good 4 hours on it today and ended right back where I started. So, when I get to work tomorrow, I will most definitely take your example and see what I can get done with it. Tomorrow is the students’ submission deadline, and we have to compile results Monday. Thank you again, SO very much, all of you, for your help.

[NeoPa]

I'm glad that's been a little helpful at least.

I suspect Luuk's post was more pertinent to your actual issue but it never hurts to drop in some ideas from time to time, especially if there are indications these may be new ideas for the OP reading them.

[Rabbit]

If you're only looking for direct trades, then combining the join with a weight for their different choices will work.

If you're allowing for cycles of trades where a goes to b, b goes to c, and c goes to a, then one algorithm you can use is the Hungarian Algorithm. Where the "cost" in this case is school preference. If I recall correctly, some nursing schools use this to assign their graduates to their choice of hospitals.

Since these trades are different than merely assigning choices by preference, you will need to preprocess the data to weed out the candidates that have 0 matches for the school they're currently in.