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.

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));

[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 responsibilitie s - 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.